question

Papillon28-9167 avatar image
0 Votes"
Papillon28-9167 asked Papillon28-9167 commented

Addition Substraction based on comparator sign with mixed data.

Hello All,

Need your help. I have source column data having mixed values as below and need output as shown in Result column

  • DDL and sample data population, start
    DECLARE @tbl TABLE (tokens VARCHAR(100));
    INSERT INTO @tbl (tokens) VALUES
    ('>4.2'),
    ('<6.0'),
    ('5.0 is max'),
    ('dup'),
    ('&5'),
    ('1');

  • DDL and sample data population, end

Result
4.3
5.9
5.0 is max
dup
&5
1


If it sees numeric data with Greater Than then it should add 0.1
If it sees numeric data with Less Than then it should substract 0.1
Rest all data remain as it is.

I am using SQL Server 2016 version. Table data having millions of rows.

T.I.A

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Papillon28-9167 commented

Seems to be simple:

 select *,
     case 
     when tokens like '>%' then cast(cast(stuff(tokens, 1, 1, '') as float) + 0.1 as varchar(100))
     when tokens like '<%' then cast(cast(stuff(tokens, 1, 1, '') as float) - 0.1 as varchar(100))
     else tokens end as Result
 from @tbl


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IIt worked! Thank you!!

0 Votes 0 ·