find if there is a 6 digit number within a string

Question

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

I just thought of replacing digits by a character or a symbol like & and check if there is &&&&&& within the string. if there's a more elegant solution let me know please — user3652812, Sep 17 '14 at 14:49
By the way, do you know how to replace all digits at once within a single 'replace' function (so I don't have to use 10 'REPLACE' for each digit)? Thanks — user3652812, Sep 17 '14 at 14:51
possible duplicate of [Check if a string contains a substring in SQL Server 2005, using a stored procedure](http://stackoverflow.com/questions/2505923/check-if-a-string-contains-a-substring-in-sql-server-2005-using-a-stored-proced) — Ryan Gates, Oct 13 '14 at 17:33

DavidG · Accepted Answer · 2014-09-17T14:55:50.070

14

You can use the LIKE operator:

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

edited Sep 17 '14 at 14:55

answered Sep 17 '14 at 14:50

DavidG

113,891
12
217
223

@sgeddes Haha only by a few seconds though! – DavidG Sep 17 '14 at 14:51
I think this will match 7 digits rather than 6. – Ankit Bajpai Sep 17 '14 at 14:54
1

@AnkitBajpai good spot! Obviously I was too eager with the Ctrl-V :) – DavidG Sep 17 '14 at 14:54
1

@DavidG I corrected while you where answering Ankit :) Hoops I looked at history and I made the mistake. My bad, sorry – bokan Sep 17 '14 at 14:56
@DavidG : is it possible to use this syntax (or similar) in a replace statement ? Looks like someone want to hide information. – bokan Sep 17 '14 at 15:00
@bokan I don't think so, but you could do it with `PATINDEX` and `SUBSTRING` – DavidG Sep 17 '14 at 15:02
@bokan Something like this `SUBSTRING(MyColumn, 0, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9]%', MyColumn)) + SUBSTRING(MyColumn, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9]%', MyColumn)+6, LEN(MyColumn))` – DavidG Sep 17 '14 at 15:07
1

This will match numbers with **at least 6 digits**. If the user needs exactly 6 digits, you'll need to change the query. – Wagner DosAnjos Sep 17 '14 at 15:23
@wdosanjos Indeed, unfortunately the OP is a bit vague. – DavidG Sep 17 '14 at 15:26

score 1 · Answer 2 · answered Sep 17 '14 at 15:10

1

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))

answered Sep 17 '14 at 15:10

Karthik Ganesan

4,142
2
26
42

Anon · Answer 3 · 2014-09-17T15:40:30.840

0

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

edited Sep 17 '14 at 15:40

answered Sep 17 '14 at 15:30

Anon

10,660
1
29
31

find if there is a 6 digit number within a string

3 Answers3

Linked