Reputation: 691
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
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
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
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
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
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