Finding numeric values in varchar field

Question

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

is the number of digits between each hyphen constant. This is a big question — S3S
– S3S, Commented Jun 11, 2018 at 15:27
SQL Server doesn't have decent regex functions, you might want to split by hyphens and validate each number separately. You need this regex pattern: [0-9]+-[0-9]+-[0-9]+ — EzLo
– EzLo, Commented Jun 11, 2018 at 15:27
DhruvJoshi- I'm using SQL Server 2012 scsimon- Yes they should be constant — Jack T
– Jack T, Commented Jun 11, 2018 at 15:31

Gordon Linoff · Accepted Answer · 2018-06-11 15:40:03Z

1

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.

edited Jun 11, 2018 at 15:40

answered Jun 11, 2018 at 15:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jyao · Accepted Answer · 2018-06-12 16:03:11Z

1

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:

edited Jun 12, 2018 at 16:03

answered Jun 11, 2018 at 15:28

jyao

1,6503 gold badges21 silver badges27 bronze badges

4 Comments

EzLo Over a year ago

This will return incorrect match with more than 3 hyphens.

jyao Over a year ago

Sounds strange to me, @EzLo, can you give an example? To me, it does not matter how many hyphens you have as all hyphens will be removed by the [replace] function.

jyao Over a year ago

Oh, I guess, @EzLo you are referring to Gordon's answer. :-)

EzLo Over a year ago

the OP wants to validate against a pattern with 3 hyphens, no more no less

Collectives™ on Stack Overflow

Finding numeric values in varchar field

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related