Asakura Asakura - 2 months ago 9
SQL Question

trim sql variant

I have column sql variant, which has the following meanings:

100, 150, D1

I'm trying to convert all numbers in the columns into letters (such as
D1
) according to specific logic in case when. But
150
has spaces and the
CASE WHEN
doesn't work.

Here's the query I'm using:

Select *,
Case When LTrim(Cast(AttributeValue As NVarchar(Max))) Between 0 And 200 Then 'D1'
Else 'Other'
End
From MyTable As SCR With (NoLock);


I tried the following

LTRIM(CAST column AS VARCHAR(MAX))


but now I get error:


Conversion failed when converting the nvarchar value 'D1' to data type int


How can I remove spaces from sql_variant?

Answer

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 varchar first.

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
Comments