sql-servercasessmsspecial-charactersunicode-escapes

How to incorporate unicode character in a CASE statement in SQL Server?


I have a column of type 'nvarchar' where some of the values have special characters. What I want to do is, if a value starts with a special character, then perform a function to remove that character, else return the value as is. The sample data is as follows:

ID Amount
1 999.09999.09
2 339.58339.58
3 2141.12055.72357.6
4 519.32519.32
5 661.84661.84
6 843.59843.59

I tried to use the 'STUFF' function to replace a special character whenever a value starts with it. i-e,

SELECT  ID,
        STUFF (Amount, 1, 1, '') AS Test,
FROM        table

I works for individual value.

But when I apply this to the whole column using CASE statement (since all values don't start with a special character), then I fail to incorporate the special character in LIKE operator. I tried the following query:

SELECT  ID,
        CASE
            WHEN Amount LIKE N'%'
            THEN (STUFF (Amount, 1, 1, ''))
            ELSE Amount
        END AS Test,
        
FROM        table

Results Expected:

ID Amount Test
1 999.09999.09 999.09999.09
2 339.58339.58 339.58339.58
3 2141.12055.72357.6 2141.12055.72357.6
4 519.32519.32 519.32519.32
5 661.84661.84 661.84661.84
6 843.59843.59 843.59843.59

The data looks like:

enter image description here


Solution

  • As your data is either numerics or special characters you could just check for it not being a number:

    case when Amount not like N'[0-9]%' then (Stuff (Amount, 1, 1, '')) else Amount end as Test