I have column sql variant, which has the following meanings:
100, 150, D1
Case When LTrim(Cast(AttributeValue As NVarchar(Max))) Between 0 And 200 Then 'D1'
From MyTable As SCR With (NoLock);
LTRIM(CAST column AS VARCHAR(MAX))
Conversion failed when converting the nvarchar value 'D1' to data type int
As per your comments edited to use BIGINT due to having larger numbers and leave the column the same if it is not BETWEEN 0 and 400
SELECT * ,CASE WHEN ISNUMERIC(LTRIM(CAST(AttributeValue AS NVARCHAR(MAX)))) = 1 AND CAST(LTRIM(CAST(AttributeValue AS NVARCHAR(MAX))) AS BIGINT) BETWEEN 0 AND 400 THEN 'D1' ELSE AttributeValue END FROM MyTable AS SCR WITH (NOLOCK)
You can use the
ISNUMERIC() function to determine which of your
sql_variants are integers and which are not.
The reason your code is failing isn't because of the trim it is because you are comparing a VARCHAR with an INTEGER so SQL is trying to automatically re cast your final string as an integer which in the case of D1 is not numeric so it causes a conversion error.
Also note that you cannot use
sql_variant directly in the
ISNUMERIC() function so cast to a
Here is an entire example of you to show you how it works:
DECLARE @MyTable AS TABLE (AttributeValue SQL_VARIANT) INSERT INTO @MyTable VALUES (CAST(' 150' AS VARCHAR(100))) ,(CAST('D1' AS VARCHAR(100))) SELECT * ,CASE WHEN ISNUMERIC(LTRIM(CAST(AttributeValue AS NVARCHAR(MAX)))) = 1 AND CAST(LTRIM(CAST(AttributeValue AS NVARCHAR(MAX))) AS INT) BETWEEN 0 AND 200 THEN 'D1' ELSE 'Other' END FROM @MyTable AS SCR