Split string without string_split (Sever 2012)

Question

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.

What research have you done? What did you try? Where didn't it work? This is a very common problem. — Dale K, Mar 29 '21 at 09:30
I don't understand the logic here. YOu need to be far more explicit here. What does `STRING_SPLIT` have to do with the question, when it *splits* a *string* into rows, based on a delimiter. That doesn't appear to be what you are after here at all. — Thom A, Mar 29 '21 at 09:30
What is the logic behind the desired split? Is the desired number allways on the same position (length of string, after x appearance of - or whatever)? If so you should have a look on `SUBSTRING` which looks far more adequate for the task at hand... — Tyron78, Mar 29 '21 at 09:36
I'm thinking that you are regarding this as a hyphen delimited string and that you always want to extract the substring which is between the second an third hyphens. Is that correct? Are you against creating a function for this as the title of the linked SO suggests? You might find this helpful: https://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string — Steve Lovell, Mar 29 '21 at 10:04

score 1 · Accepted Answer · answered Mar 29 '21 at 10:07

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.

score 1 · Answer 2 · answered Mar 29 '21 at 11:45

1

Try the below query

select name, SUBSTRING(rst, 1, patindex('%[-]%',rst)-1) as Number
from (
select name, SUBSTRING(name, patindex('%[-][0-9]%',name)+1, 100)  rst        
from tabOld old 
)a

answered Mar 29 '21 at 11:45

B.Muthamizhselvi

642
4
13

Split string without string_split (Sever 2012)

2 Answers2