SQL: get a substring using a delimiter

Question

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

Does this answer your question? [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) — Stoff, Jul 08 '22 at 21:19
Failed to execute query. Error: 'STRING_SPLIT' is not a recognized built-in function name. — user09, Jul 08 '22 at 21:29
Microsoft SQL Azure (RTM) - 12.0.2000.8 May 12 2022 23:11:24 Copyright (C) 2022 Microsoft Corporation — user09, Jul 08 '22 at 21:36

score 3 · Accepted Answer · answered Jul 08 '22 at 21:21

3

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

answered Jul 08 '22 at 21:21

John Cappelletti

79,615
7
44
66

Can confirm that this is how I'd do it on MSSQL. – Stoff Jul 08 '22 at 21:25
@Stoff Yes, SQL Server 2016+ Take a peek at https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=0c5ae1432cd383d4ca5e188af3e5884e – John Cappelletti Jul 08 '22 at 21:26
@Stoff Easy to expand ... note the pattern /index https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=4d5e4a48e730995d31c9228cb8c22fab – John Cappelletti Jul 08 '22 at 21:29
@JohnCappelletti this works. can you pls explain – user09 Jul 08 '22 at 21:37
1

@John Capalletti, you misunderstand - I AM confirming that this works. No need for me to take a peek. Like stated, I CONFIRM that this is how I would do it in MSSQL aswell. – Stoff Jul 08 '22 at 21:38
1

@Stoff Ahh. Got it. – John Cappelletti Jul 08 '22 at 21:38
2

@user09 The CROSS APPLY converts you [column] into a JSON string. Then it is a small matter to grab the INDEX 1 via json_value() – John Cappelletti Jul 08 '22 at 21:40
@user09 take a moment and look at the 2nd dbFiddle link in the comments above. – John Cappelletti Jul 08 '22 at 21:42
how can I add where condition to this query like ```where Pos2 not in ('ABCD', 'IOP')``` – user09 Jul 11 '22 at 20:02
1

@user09 Just add WHERE JSON_VALUE(S,'$[1]') not in ('ABCD', 'IOP') – John Cappelletti Jul 11 '22 at 20:12
@user09 If you don't want to get JSON_VALUE(...) twice, you can use a second CROSS APPLY and ref Pos2 Exmple: https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=19c70df41098d2fd51a3ee69d20ca0e5 – John Cappelletti Jul 11 '22 at 20:16
appreciate any help on this https://stackoverflow.com/questions/73028249/spring-jdbctemplate-use-query-with-json-as-input-for-jdbc-tempalte – user09 Jul 19 '22 at 18:44

SQL: get a substring using a delimiter

1 Answers1