Perform the next simple query:

with tt1 as (
    SELECT CASE WHEN TRY_CAST (QUESTIONETAILUALUE AS INT) IS NULL THEN N'BAD 'ELSE N'OK' END AS TRYCAST
    From vw.rates)
Select Trycast.
From TT1
--Where trycast = n'bad '

The result of this request is only the answer ‘OK’, which indicates that only numeric values ​​are contained in the QuestionDetailValue column. Also, I tried to get Distinct QuestionDetailValue. The result is numbers from 0 to 10. And it is correct.

But as soon as I unconscribe the WHERE filter and try to filter, say, values ​​& gt; = 9, pops up error:

“Message 245, Level 16, Condition 1, Row 225
Conversion Failed WHEN Converting The Nvarchar Value ‘👍👍👍👍👍👍’ To Data Type Int. “

And no matter what I do, I can’t miss her. I tried try_cast, try_convert, Case When, Cast, Convert and so on. But I can’t get around this error known to me.

And most importantly – I can’t catch a line with this error. It is simply not. The filter for these symbols gives simply the same error, and that’s it. Accordingly, for the same reason, I can’t throw here about the approximate Create Table, because I can’t catch these characters.

Tell me what can be done?

Answer 1

Try the following solution.

sql

- DDL and Sample Data Population, Start
Declare @rates Table (ID Int Intentity Primary Key, QuestionDetailValue Nvarchar (255));
INSERT INTO @Rates Values
(N'10 '),
(N'👍👍👍👍👍👍 ');
- DDL and Sample Data Population, End
SELECT *
From @rates.
  Cross Apply (QuestionDetailValue AS INT) is null, n'bad ', n'ok'))) AS T (C)
--Where C = N'Bad ';

result

+ ---- + ------------------ --- + ----- +
| ID | QuestionDetailValue | C |
+ ---- + --------------------- + ----- +
| 1 | 10 | OK |
| 2 | 👍👍👍👍👍👍 | Bad |
+ ---- + --------------------- + ----- +

after the offer of offer WHERE

+ ---- + ------------------ --- + ----- +
| ID | QuestionDetailValue | C |
+ ---- + --------------------- + ----- +
| 2 | 👍👍👍👍👍👍 | Bad |
+ ---- + --------------------- + ----- +

Answer 2

Select Count (QuestionDetailValue) from TT1 WHERE QUESTIONETAILUALUE & GT; = 9

“Message 245, Level 16, Condition 1, Row 225 Conversion Failed WHEN Converting The Nvarchar Value ‘👍👍👍👍👍👍’ To Data Type int.”

Most likely you need to use a request with such a condition:

where try_cast (QuestionDetailValue AS INT) & GT; = 9