Split string without string_split (Sever 2012) [closed]

I would like to know if and if it’s possible how to split a string in to different columns.

So first of all I have the table tabOld which contains:

name
5522-rep__-4210-03-test-434907-emn-nt.pdf

and the other table tabNew:

name	number
5522-rep__-4210-03-test-434907-emn-nt.pdf	4210

So what I want to do is to copy the name from tabOld to tabNew and additionally split the 4210 from the pdf:

5522-rep__-4210-03-test-434907-emn-nt.pdf

into the column number in table tabNew via an SQL script.

I allready tried it with How Do I Split a Delimited String in SQL Server Without Creating a Function? but that didn’ work.

Answer

Assuming you always need the third substring from the name column by splitting on -, then you could indeed use the XML based solution from the post you found earlier.

I used some CTE’s (common table expressions) to isolate the subqueries.

Sample data

create table tabOld
(
  name nvarchar(200)
);

insert into tabOld (name) values
('5522-rep__-4210-03-test-434907-emn-nt.pdf');

create table tabNew
(
  name nvarchar(200),
  num int
);

Solution

with cteXml as
(
  select old.name,
         convert(xml, '<r>'+replace(old.name, '-', '</r><r>')+'</r>') as XMLCol
  from tabOld old
),
cteSplit as
(
  select cx.name,
         row_number() over(order by (select null)) as Num,
         n.r.value('.', 'nvarchar(200)') as SubStr
  from cteXml cx
  cross apply cx.XMLCol.nodes('r') as n(r)
)
insert into tabNew (name, num)
select cs.name,
       convert(int, cs.SubStr)
from cteSplit cs
where cs.Num = 3;

Result

name                                       num
-----------------------------------------  ----
5522-rep__-4210-03-test-434907-emn-nt.pdf  4210

Fiddle to see things in action.