CODEWITHSUNDEEP
sqlsql-serversql-server-2016
AskMe
AskMe

Reputation: 2571

Find records having one or two numeric values from a column

I have a column like this in one of my SQL server table.

            column
            ------
            ABC COLLA #1                                                                                        
            SF6 DINGS                                                                                          
            MEMORY NO. 2                                                                                       
            MEMORY NO1, TATA
            ABC PIKUS NO 2 
            39A NY ROAD, S/F'DOK 
            CHINA/ PPS#494030750 
            KIKU /769-0133

I would like to find the records having one or two numeric values only. Hence my final column will look like below.

            column
            ------
            ABC COLLA #1                                                                                        
            SF6 DINGS                                                                                          
            MEMORY NO. 2                                                                                       
            MEMORY NO1, TATA
            ABC PIKUS NO 2 
            39A NY ROAD, S/F'DOK

In this case, the numeric position is any where in the string. I know, if it could have 1st or last digit then I could have used, 

column LIKE '%[0-9]' OR LIKE '[0-9]%'

However, in this case the number could be at any position in the string.As position is not fixed, using SUBSTRING will not help. How can I get the desigred result? Thanks.

Upvotes: 2

Views: 245

Answers (2)

Thom A
Thom A

Reputation: 95589

Like, this?

WITH VTE AS(
SELECT *
FROM (VALUES('ABC COLLA #1'),                                                                                  
            ('SF6 DINGS'),                                                                                  
            ('MEMORY NO. 2'),                                                                                       
            ('MEMORY NO1, TATA'),
            ('ABC PIKUS NO 2'), 
            ('39A NY ROAD, S/F''DOK'),
            ('CHINA/ PPS#494030750'), 
            ('KIKU /769-0133')) V([Column]))
SELECT *
FROM VTE
WHERE [Column] NOT LIKE '%[0-9]%[0-9]%[0-9]%' --3 or more numerics is ok
  AND [Column] LIKE '%[0-9]%'; --but Contains numerics

Edit: it's worth mentioning that, for large datasets without fulltext indexing, this is going to be show. Queries like this, with WHERE clauses with leading wildcards aren't SARGable; meaning a full scan is going to be needed of every row.

Upvotes: 2

S3S
S3S

Reputation: 25122

Using patindex ... thanks to Larnu for the sample data

WITH VTE AS(
SELECT *
FROM (VALUES('ABC COLLA #1'),                                                                                  
            ('SF6 DINGS'),                                                                                  
            ('MEMORY NO. 2'),                                                                                       
            ('MEMORY NO1, TATA'),
            ('ABC PIKUS NO 2'), 
            ('39A NY ROAD, S/F''DOK'),
            ('CHINA/ PPS#494030750'), 
            ('KIKU /769-0133')) V([col1]))
select *
from VTE
where ( 
         PATINDEX('%[0-9]%',col1) > 0  --1 numeric
      or PATINDEX('%[0-9]%',SUBSTRING(col1,PATINDEX('%[0-9]%',col1),999)) > 0 --two numberics
      )
      and
      PATINDEX('%[0-9]%',SUBSTRING(col1,PATINDEX('%[0-9]%',col1) + PATINDEX('%[0-9]%',SUBSTRING(col1,PATINDEX('%[0-9]%',col1),999)) + 1,999)) = 0 --3 numerics or more

Upvotes: 2

Related Questions