CODEWITHSUNDEEP
sqlsql-servert-sql
Jack T
Jack T

Reputation: 15

Finding numeric values in varchar field

sorry if this is a duplicate, I wasn't able to find what I was looking for in the answered questions.

I'm looking to query for only records with a field formatted like this numbers (0-9), hyphen (-), number (0-9), hyphen (-), numbers (0-9). This is what I have tried:

    SELECT *
    FROM TABLE_1
    WHERE LTRIM(RTRIM(LOC_NAME)) LIKE '[0-9]-[0-9]-[0-9]'

The result set I'm looking for would be 123456-123-1234. I thought at first they may have spaces so I trimmed the field but still no results are showing with the ABOVE query. The BELOW query returns them, but with other results:

    SELECT *
    FROM TABLE_1
    WHERE LOC_NAME LIKE '%[0-9]-[0-9]%'

But I would get results like 1-2-3 Place...

Upvotes: 1

Views: 481

Answers (2)

jyao
jyao

Reputation: 1630

I would do this way

select *
from table_1
where isnumeric(replace(LOC_NAME, '-','')) = 1;

Update (2018-Jun-12)

After reading the comments of @EzLo, I realized that the OP may just need two hyphens (no more, no less), so I am updating my answer with the following demo code

create table #t (LOC_NAME varchar(100));
go

insert into #t (loc_name) 
values ('a12-b12-123'), ('123456-123-11'), ('123-123-123-123')
go

select *
from #t --table_1
where isnumeric(replace(LOC_NAME, '-','')) = 1
and len(loc_name)-len(replace(LOC_NAME, '-',''))=2

The result is:

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I think this does what you want:

SELECT *
FROM TABLE_1
WHERE LTRIM(RTRIM(LOC_NAME)) NOT LIKE '%[^-0-9]%'

This checks that the field has no non-hyphens or non-digits.

If you specifically want two hyphens, separated by digits, then:

WHERE LTRIM(RTRIM(LOC_NAME)) NOT LIKE '%[^-0-9]%' AND
      LTRIM(RTRIM(LOC_NAME)) LIKE '[0-9]%-[0-9]%-[0-9]%' AND
      LTRIM(RTRIM(LOC_NAME)) NOT LIKE '%-%-%-%'

The second pattern requires at least two hyphens and a digit in all three parts of the name. The third forbids three hyphens.

Upvotes: 1

Related Questions