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:

CodePudding user response：

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

CodePudding user response：

Loosing the other characters can be done like this:

WITH nrs as (
    SELECT 1 as x 
    union all
    select x 1 from nrs where x<25
  ),
cte as 
        (select Amount,substring(Amount,1,1) as a from mytable
         union all
         select Amount,substring(Amount,x 1,1) as a
         from mytable
         cross apply nrs x)
select mytable.Amount, string_agg(a,'') as NewNumber
from mytable
inner join cte on cte.Amount = mytable.Amount and a<>''
where a between '0'and '9' or a='.' or a=','
group by mytable.Amount;

output:

Amount	NewNumber
?339.58?339.58	339.58339.58
?519.32?519.32	29.312539.15
?661.84?661.84	66.186468.14
?843.59?843.59	93.549853.48
?999.09?999.09	9990.99990.9
2141.1?2055.72?357.6	27357.6.55021.1421

see: DBFIDDLE

NOTE: NewNumber is still not numeric! It just contains numbers and/or a '.' and/or a ','.

CodePudding user response：

You can use TRIM - for SQL Server 2019 and below, TRIM removes the leading and trailing characters. For SQL Server 2022 you can specify leading/trailing/both:

Sample Data:

Declare @testData Table (ID int, Amount nvarchar(50));
 Insert Into @testData (ID, Amount)
 Values (1, N'999.09999.09')
      , (2, N'339.58339.58')
      , (3, N'2141.12055.72357.6')
      , (4, N'519.32519.32')
      , (5, N'661.84661.84')
      , (6, N'843.59843.59');

SQL Server 2017:

 Select *
      , Test = trim(N'' From td.Amount)
   From @testData td;

SQL Server 2022:

 Select *
      , Test = trim(leading N'' From td.Amount)
   From @testData td;