trim sql variant

Question

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?

SELECT *, CASE WHEN LTRIM(CAST(AttributeValue AS NVARCHAR(MAX))) BETWEEN 0 AND 200 THEN 'D1' ELSE AttributeValue END FROM MyTable AS SCR WITH (NOLOCK) — Asakura
@iDevlop yes there is. sql_variant is a sql-server data type it isn't great to use it but it does exist. msdn.microsoft.com/en-us/library/ms173829.aspx — Matt

Matt Matt · Accepted Answer · 2016-09-19T15:30:32

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

trim sql variant

2 Answers