How to fetch only a part of string

user_4858552 0
Viewed 550

I have a column which has inconsistent data. The column named ID and it can have values such as

0897546321
ABC,0876455321
ABC,XYZ,0873647773
ABC,
99756
test only

The SQL query should fetch only Ids which are of 10 digit in length, should begin with a 08 , should be not null and should not contain all characters. And for those values, which have both digits and characters such as ABC,XYZ,0873647773, it should only fetch the 0873647773 . In these kind of values, nothing is fixed, in place of ABC, XYZ , it can be anything and can be of any length.

The column Id is of varchar type.

My try: I tried the following query

select id
from table
where id is not null
and id not like '%[^0-9]%'
and id like '[08]%[0-9]'
and len(id)=10

I am still not sure how should I deal with values like ABC,XYZ,0873647773

P.S - I have no control over the database. I can't change its values.

sql-server sql
2 Answers

SQL Server generally has poor support regular expressions, but in this case a judicious use of PATINDEX is viable:

SELECT SUBSTRING(id, PATINDEX('%,08[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9],%', ',' + id + ','), 10) AS number
FROM yourTable
WHERE ',' + id + ',' LIKE '%,08[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9],%';

screen capture from demo link below

Demo

user_18632290

If you normalise your data, and split the delimited data into parts, you can achieve this some what more easily:

SELECT SS.value
FROM dbo.YourTable YT
     CROSS APPLY STRING_SPLIT(YT.YourColumn,',') SS
WHERE LEN(SS.value) = 10
  AND SS.value NOT LIKE '%[^0-9]%';

If you're on an older version of SQL Server, you'll have to use an alternative String Splitter method (such as a XML splitter or user defined inline table-value function); there are plenty of examples on these already on Stack Overflow.

db<>fiddle

user_20299830
Related