Split a string by a delimiter using SQL

Question

My table in DB has a column which stores values in following format.

1234#2345#6780

Four digit numbers are stored using delimiter "#". Due to a data corruption, there are some records with five digit numbers. There may be one or more than one five digit numbers in a given row.

1234#12345#67895

I'm trying to write a script to get only those corrupted records But cannot find a way to split and check values.

Any help is appreciated.

I'm using SQL server 12.0 version

But cannot find a way to split and check values what do you mean by that — Nikhil S, Dec 04 '18 at 05:42
If you were using SQL Server 2016 or later, then you would be able to use `STRING_SPLIT`. Given that you are using 2012, you may have to write a custom UDF to do this, [see here](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns). — Tim Biegeleisen, Dec 04 '18 at 05:43
@nikhilsugandh split and check if one or more numbers have length more than 4 — pavithraCS, Dec 04 '18 at 05:43

score 2 · Answer 1 · answered Dec 04 '18 at 05:52

you can use this function to split values:

CREATE FUNCTION [dbo].[fnSplit] 

(@sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = '#' -- delimiter that separates items
) 

RETURNS @List TABLE (item VARCHAR(8000))

BEGIN

DECLARE @sItem VARCHAR(8000)

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0

 BEGIN

 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),

 @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem as item
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList as item -- Put the last item in
RETURN
END

and then ask about the result

Nikhil S · Answer 2 · 2018-12-04T06:13:38.050

1

this will work patindex is orcale's equivalent of regexp_like():

select * from table_name where not PATINDEX ('^[0-9]{4}(#){1}[0-9]{4}(#){1}[0-9] 
{4}$',col_name)  !=0;

edited Dec 04 '18 at 06:13

answered Dec 04 '18 at 05:49

Nikhil S

3,786
4
18
32

score 1 · Answer 3 · answered Dec 04 '18 at 05:50

You can use XML nodes to split the string before 2016 version.

Create table Xmltest(ID int, numbers nvarchar(max)) 
insert into Xmltest values  (1, '1234#12345#67895') 

select ID,  N.value('.', 'varchar(255)') as xmlValue
from (

select ID , 
cast(('<w>' + replace(numbers,'#','</w><w>') + '</w>') as xml) as xmlValue
from Xmltest

) as z
cross apply xmlValue.nodes ('//w') as split(N)

Output you get, I added this ID column to identify which row may have more than 4 Characters.

 ID xmlValue
 1  1234
 1  12345
 1  67895

To check where you have more than 4 characters you can do:

select ID,  N.value('.', 'varchar(255)') as xmlValue
from (

select ID , 
cast(('<w>' + replace(numbers,'#','</w><w>') + '</w>') as xml) as xmlValue
from Xmltest

) as z
cross apply xmlValue.nodes ('//w') as split(N)
where len(N.value('.', 'varchar(255)')) > 4

Output you get:

ID  xmlValue
1   12345 
1   67895

score 1 · Answer 4 · answered Dec 04 '18 at 06:03

1

You can use this. it returns any numbers row which length greater than 4.

SELECT * FROM SampleData
WHERE  data LIKE '%[0-9][0-9][0-9][0-9][0-9]%'

answered Dec 04 '18 at 06:03

Serkan Arslan

13,158
4
29
44

Dyrandz Famador · Answer 5 · 2018-12-04T08:14:37.063

0

for SQL Server (starting with 2016) you can use the built in function of SQL to split a string.

sample:

DECLARE @Text VARCHAR(100) = '1234#12345#67895'

SELECT * FROM STRING_SPLIT(@Text,'#')

result:

value
----
123
4456
78902

you can now easily manipulate the values after

edited Dec 04 '18 at 08:14

answered Dec 04 '18 at 08:09

Dyrandz Famador

4,499
5
25
40

Split a string by a delimiter using SQL

5 Answers5