CODEWITHSUNDEEP
sqlsql-server
user09
user09

Reputation: 956

SQL: get a substring using a delimiter

I have column which has - as delimiter and I want to get substring between the first and second delimiter:

ABC-DEF-FG
ABCD-JAFF-UIOU-TYU

Output

DEF
JAFF

I have tried substring and charIndex but not getting exactly what I wanted

select * SUBSTRING(column, CHARINDEX('-', column)+1, len(column)) 
from table

select * SUBSTRING(column, CHARINDEX('-', column)+1, charindex('-', column, (charindex('-', column, 1)))) 
from table

select * SUBSTRING(column, CHARINDEX('-', column)+1, charindex('-', column, (charindex('-', column, 1))+1)) 
from table

Upvotes: 1

Views: 156

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

With a bit of JSON

Example

Select A.[column] 
      ,Pos2  = JSON_VALUE(S,'$[1]')
From  YourTable A
Cross Apply ( values ( '["'+replace(string_escape([column],'json'),'-','","')+'"]' ) ) B(S)

Results

column              Pos2
ABC-DEF-FG          DEF
ABCD-JAFF-UIOU-TYU  JAFF

Upvotes: 3

Related Questions