SQL server split string into columns by delimiter (dynamic length)

Question

SQL server cannot use MySQL split_index function, and my environment face accessibility blocking to use some function on server like "CREATE" "INSERT"

Are there any method to split strings by fixed delimiter into columns ? Has 3 delimiters but length is dynamic. e.g. STRING : sometimes - "AA.0.HJ", sometimes - "AABBCC.099.0",sometimes - "0.91.JAH21"

The combinations of substring is not work.

SUBSTRING(STRING , 
            CHARINDEX('.', STRING )+1, 
            LEN(STRING )-CHARINDEX('.', STRING )
Origin:
STRING 
AA.0.HJ
AABBCC.099.0
0.91.JAH21

Target :
STRING        First    Second  Third
AA.0.HJ       AA       0       HJ
AABBCC.099.0  AABBCC   099     0
0.91.JAH21    0        91      JAH21

What is the solution in this situation ?

For something as simple as your sample data I would use the PARSENAME solution below. — Sean Lange, Nov 13 '19 at 14:17

score 7 · Accepted Answer · answered Nov 13 '19 at 11:22

7

An xml-based solution

declare @tmp table (STRING varchar(500))

insert into @tmp
values
 ('AA.0.HJ')
,('AABBCC.099.0')
,('0.91.JAH21')

;WITH Splitted
AS (
    SELECT STRING
        ,CAST('<x>' + REPLACE(STRING, '.', '</x><x>') + '</x>' AS XML) AS Parts
    FROM @tmp
    )
SELECT STRING
    ,Parts.value(N'/x[1]', 'varchar(50)') AS [First]
    ,Parts.value(N'/x[2]', 'varchar(50)') AS [Second]
    ,Parts.value(N'/x[3]', 'varchar(50)') AS [Third] 
FROM Splitted;

Output:

answered Nov 13 '19 at 11:22

Andrea

11,801
17
65
72

Thanks Andrea its a brilliant method but how to apply my real column from the table into "values" ? When i putting the part from WITH statement , have no idea what to put after "Parts.xxxx" as putting real column name after "Parts.REAL_COLUMN" gives error – rane Nov 14 '19 at 02:15
1

Worked , Andrea . By putting SELECT REA_COLUMN.value(N'/x[1]', 'varchar(50)') as [first] after quotation 't' of CAST() XML chain . Perfect – rane Nov 14 '19 at 02:53

score 5 · Answer 2 · answered Nov 13 '19 at 10:50

5

You can use parsename

Declare @t table (name varchar(50))

insert into @t values ('AA.0.HJ')
insert into @t values ('AABBCC.099.0')

select parsename(name,3),parsename(name,2),parsename(name,1) from @t

answered Nov 13 '19 at 10:50

Red Devil

2,343
2
21
41

Thank you for your answer , but i get all NULL value return on applying select parsename(name,x) on my real table and column . Should add some data type changes on that ? – rane Nov 14 '19 at 01:53
works with dot '.', but not dash '-' – Curious Watcher Nov 22 '22 at 16:37

SQL server split string into columns by delimiter (dynamic length)

2 Answers2

Linked

Related