How to combine If statement with Substring command in SQL?

Question

I have two types of entries in one column. One type starts with V2, the other starts with a digit. How do I write a query in SQL where I can extract different part of the string based on how it starts?

TextStringColumn
V2|T:GSK|1000000|S1:TES|S2:N/A|Q:24|S:0.5gx3|PD:2020-10-22|C:QQ
2000308|S1:BES|T:SKY|Q:16446G|BSI:BPKGVAXQOHZFWGE

I wrote

SELECT TextStringColumn, If(TextStringColumn like 'V2%',SUBSTRING(TextStringColumn ,10,7),SUBSTRING(TextStringColumn ,1,7)) As NumberCol
FROM TestTable

But I keep getting syntax errors.

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'If'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

The desired result will be

TextStringColumn                                                           NumberCol              
V2|T:GSK|1000000|S1:TES|S2:N/A|Q:24|S:0.5gx3|PD:2020-10-22|C:QQ            1000000
2000308|S1:BES|T:SKY|Q:16446G|BSI:BPKGVAXQOHZFWGE                          2000308

The official docs should always be your first port of call.

Dale K
– Dale K

2020-11-17 19:20:00 +00:00
Commented Nov 17, 2020 at 19:20 — Dale K
– Dale K, Commented Nov 17, 2020 at 19:20

Tim Biegeleisen · Accepted Answer · 2020-11-17 16:07:34Z

1

You may use a CASE expression:

SELECT
    CASE WHEN TextStringColumn LIKE 'V2%'
         THEN SUBSTRING(TextStringColumn, 10, 7)
         ELSE SUBSTRING(TextStringColumn, 1, 7) END AS NumberCol
FROM TestTable;

The above logic assumes the only two varieties of strings are those which start with V2, and those which do not start with V2.

answered Nov 17, 2020 at 16:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Cappelletti · Accepted Answer · 2020-11-17 16:23:24Z

1

If the strings are variable length ... consider a little JSON

Example

Declare @YourTable Table ([TextStringColumn] varchar(100))  Insert Into @YourTable Values 
 ('V2|T:GSK|1000000|S1:TES|S2:N/A|Q:24|S:0.5gx3|PD:2020-10-22|C:QQ')
,('2000308|S1:BES|T:SKY|Q:16446G|BSI:BPKGVAXQOHZFWGE')
 
Select A.* 
      ,Val = case when [TextStringColumn] like 'V2%'
                  then JSON_VALUE(S,'$[2]')
                  else JSON_VALUE(S,'$[0]') end
 From @YourTable A
 Cross Apply ( values ( '["'+replace([TextStringColumn],'|','","')+'"]' ) ) B(S)

Returns

TextStringColumn                                                    Val
V2|T:GSK|1000000|S1:TES|S2:N/A|Q:24|S:0.5gx3|PD:2020-10-22|C:QQ     1000000
2000308|S1:BES|T:SKY|Q:16446G|BSI:BPKGVAXQOHZFWGE                   2000308

edited Nov 17, 2020 at 16:23

answered Nov 17, 2020 at 16:17

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Collectives™ on Stack Overflow

How to combine If statement with Substring command in SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related