Extracting substring between two characters (commas)

Question

I need to extract codes from a string separated by commas.

I have figured out how to extract the first code but i'm struggling trying to figure out how to extract subsequent codes.

Example string:

~R10.4,T85.8,Y83.8,I10X,I25.9,E10.9,Z95.5,Z93.2,Z88.0

Query:

select code_string, substring(code_string, 0, charindex(',',code_string)) as first
FROM TABLE

Returns:

~R10.4

I now need to extract subsequent codes

~R10.4
T85.8
Y83.8
I10X
etc.

any help would be greatly appreciated

I would prefer not to have to create a function if possible

Which dbms are you using? (Many products have their own string functions, like charindex etc.) — jarlh, Nov 27 '19 at 15:30
Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — EzLo, Nov 28 '19 at 08:47

score 1 · Answer 1 · answered Nov 27 '19 at 15:46

1

I guess you're using SQL server if so you can use

SELECT value FROM
STRING_SPLIT('~R10.4,T85.8,Y83.8,I10X,I25.9,E10.9,Z95.5,Z93.2,Z88.0', ',');

answered Nov 27 '19 at 15:46

Vaghinak

535
6
12

thank you but string_split is not a built in function name for the version of SSMS I am using. – Jamie Noobin Nov 28 '19 at 08:24
You can use answers in this post to make it work https://stackoverflow.com/questions/37616831/string-split-is-not-a-recognized-built-in-function-name/41669929 – Vaghinak Nov 28 '19 at 08:30

score 0 · Answer 2 · answered Nov 28 '19 at 11:55

thought I'd share my solution

;WITH Split_Field (CODE_STRING, xmlname)
AS
(
    SELECT 
    CODE_STRING,
    CONVERT(XML,'<Names><name>'  
    + REPLACE(CODE_STRING,',', '</name><name>') + '</name></Names>') AS xmlname
      FROM 
(
Select cast('~I20.9,I10X,E78.0,H81.0,K90.0,M81.9,M19.9,Z86.7' as nvarchar(MAX)) 
/*replace with your code field*/ as CODE_STRING

--from table 

) as A

)

SELECT 
CODE_STRING, 
xmlname.value('/Names[1]/name[1]','varchar(100)') AS Col1,    
xmlname.value('/Names[1]/name[2]','varchar(100)') AS Col2,
xmlname.value('/Names[1]/name[3]','varchar(100)') AS Col3,
xmlname.value('/Names[1]/name[4]','varchar(100)') AS col4,
xmlname.value('/Names[1]/name[5]','varchar(100)') AS col5
FROM Split_Field

Extracting substring between two characters (commas)

2 Answers2