CODEWITHSUNDEEP
sqlsql-serversql-server-2012
pavithraCS
pavithraCS

Reputation: 691

Split a string by a delimiter using SQL

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

Upvotes: 2

Views: 437

Answers (5)

Dyrandz Famador
Dyrandz Famador

Reputation: 4525

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

Upvotes: 0

Nikhil
Nikhil

Reputation: 3950

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;

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13403

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]%'

Upvotes: 1

bat7
bat7

Reputation: 866

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

Upvotes: 2

Avi
Avi

Reputation: 1845

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

Upvotes: 1

Related Questions