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, Sep 19 '16 at 15:31
@iDevlop yes there is. sql_variant is a sql-server data type it isn't great to use it but it does exist. https://msdn.microsoft.com/en-us/library/ms173829.aspx — Matt, Sep 19 '16 at 15:40

Matt · Accepted Answer · 2016-09-19T16:13:59.220

4

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

edited Sep 19 '16 at 16:13

answered Sep 19 '16 at 15:30

Matt

13,833
2
16
28

I posted the whole query. When in where case I choose such rows, where sql_variant column has numbers with spaces, the logic works. But when it select rows like 'D2' I get the error – Asakura Sep 19 '16 at 15:39
@Asakura are you saying that with testing with ISNUMERIC() you are still getting the error or something different can you post what you have tried most recently that gave you the error? – Matt Sep 19 '16 at 15:42
Yes it worked, thank you! But after I changed my query according to your answer, I faced another problem. As this column is filled by software users, it has some strange values like 20001069307 and now I get error: The conversion of the nvarchar value '20001069307' overflowed an int column. Is there any fix for it? – Asakura Sep 19 '16 at 16:05
I'll write my whole task: I should convert numbers below 400 to letters like D1 according to specific logic, all other different values should remain as they were filled by software users – Asakura Sep 19 '16 at 16:08
@Asakura you can cast as BIGINT instead of INT which allows for larger numbers.. If you need to account for a decimal place then you can use a DECIMAL() instead. – Matt Sep 19 '16 at 16:11
@Asakura to get them to remain the same in the ELSE section simply put the column name. – Matt Sep 19 '16 at 16:12
it worked. Thank you very much again, you made my day! – Asakura Sep 19 '16 at 16:18
glad it worked please accept the answer so others know that you got what you needed and the reputation points get assigned here is a link with more information on how/when/why etc if you need. thanks http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Matt Sep 19 '16 at 16:21

score -2 · Answer 2 · answered Sep 19 '16 at 15:34

-2

use sql replace function select replace(columnnName, ' ', '')

answered Sep 19 '16 at 15:34

user5714525

1

When I use replace I get this error: Argument data type sql_variant is invalid for argument 1 of replace function – Asakura Sep 19 '16 at 15:41
@Asakura this wouldn't help you with the conversion issue that you are facing you have to test if the data isnumeric first. this is just another way of removing spaces but this will remove ALL spaces which happens to be okay in your case. sql_variant cannot be used in functions so you would have to cast the column to a varchar inside the replace. but again this won't help you with your data conversion error which is why it has 2 downvotes already – Matt Sep 19 '16 at 15:44

trim sql variant

2 Answers2