CODEWITHSUNDEEP
sqlsql-server
user3652812
user3652812

Reputation: 101

find if there is a 6 digit number within a string

How would you advise to find out in Sql Server 2010/2012 if a query contains a substring equal to a 6 digits number?

e.g. "agh123456 dfsdfdf" matches the requirements

"x123 ddd456" doesn't match the requirements because the 6 digits are not consecutive

"lm123" doesn't match the requirements because only 3 digits are found (out of the required 6)

The problem I encountered so far: is that SUBSTRING as a function requires parameters (position where the number presumably starts and this is random) while PATINDEX returns the location of a pattern in a string, but we don't know the exact pattern (it can be any 6 digit number)

Any pointers or advice, much appreciated.

Thank you

Upvotes: 4

Views: 30393

Answers (3)

Anon
Anon

Reputation: 10908

If you want to select all rows in the table and mask the first 6-digit substring in each row:

DECLARE @mask varchar(max) = '######'
DECLARE @pattern varchar(max) = '%'+REPLACE(@mask,'#','[0-9]')+'%'

SELECT
  ISNULL(STUFF(col1,PATINDEX(@pattern,col1),LEN(@mask),@mask),col1)
FROM Table1

Upvotes: 0

Karthik Ganesan
Karthik Ganesan

Reputation: 4222

Even this should work.. considering you don't have a string like this

abc123 abc123456

Try this

DECLARE @str varchar(max) = 'abcxyz123456'

SELECT ISNUMERIC(SUBSTRING(@str,(SELECT PATINDEX('%[0-9]%',@str)),6))

Upvotes: 1

DavidG
DavidG

Reputation: 118937

You can use the LIKE operator:

SELECT *
FROM MyTable
WHERE Mycolumn LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]%'

Upvotes: 15

Related Questions