How to check if format is correct

Question

Greetings, I need to check whether the format of value stored database is correct. It should check for something like that:

x-x
x-xx
xx-xx
xxx-xxx
etc.

where xxx should be an integer. So the concept is to check if value has the following format: Integer - Integer

Answer 1

SQL doesn't have the most powerful pattern matching. But this query should find most bad formats:

select  *
from    YourTable
where   col1 like '%[^0-9-]%'
        or col1 like '%-%-%'
        or col1 not like '%[0-9]-[0-9]%'

This works like:

• col1 like '%[^0-9-]%' there may only be numbers and dashes
• col1 like '%-%-%' there cannot be two dashes
• col1 not like '%[0-9]-[0-9]%' there must a be a digit to the left and right of the dash

Answer 2

Try this -

select CASE WHEN (
charindex('-',columnName) > 0
AND
ISNUMERIC(left(columnName,(charindex('-',columnName)-1))) > 0
AND 
ISNUMERIC(right(columnName,(charindex('-',columnName)))) > 0
) THEN 1 ELSE 0 END as properWord 
from myTable

returns 1 if proper word else returns 0

EDIT: Works assuming you do not have any strings with consecutive '-' like '--' followed by numbers on both sides. Works for all other cases.

Answer 3

create table #t (
    txt varchar(100)
)
go

insert into #t select '1-1'
insert into #t select '11-22'
insert into #t select '1-1-'
insert into #t select '1-A'
insert into #t select '1-A1'
insert into #t select '-'
insert into #t select '1-1-1'
insert into #t select '1 - 3'
go

select 
    txt
from 
    #t
where
    -- digits and dashes only
    txt not like '%[^0-9-]%' and
    -- one dash only
    len(replace(txt, '-', '')) = len(txt)-1 and
    -- starts and ends with a digit
    txt like '[0-9]%[0-9]'

That should do it, assuming that you don't have any other requirements for handling spaces or whatever, and you can make it into a CHECK constraint if you want.