April 2, 2009 at 6:55 am
Is it possible in TSQL to determine whether a value is in a specific format e.g. check if the value is in "x-xxxxx-x-xxx" format?
April 2, 2009 at 7:06 am
You can check for this kind of format. Take a look at the script bellow that shows you how to check for format. I think that it is self-explainery, but if you do have any questions feel free to ask.
create table Demo (vc varchar(15))
go
insert into Demo (vc)
select '12-3456-7890'
union
select '1a-3456-bcde'
union
select '1--3456-7890'
union
select '1-2345-6789'
--select the strings that have
--the format xx-xxxx-xxxx where x can only be numeric
select * from Demo
where vc like '[0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
--select the strings that the format xy-xxxx-yyyy
--where x can be only numeric and y can be numeric or alpha numeric (a-z)
select * from Demo
where vc like '[0-9][0-9,a-z]-[0-9][0-9][0-9][0-9]-[0-9,a-z][0-9,a-z][0-9,a-z][0-9,a-z]'
go
--clean up
drop table Demo
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 2, 2009 at 7:06 am
You can use the LIKE operator to achieve that.
For example, if your 'x' mean 'any character', you could use
like '_-_____-_-___' to test.
If you need some characters to be in a specific range, you can use the [] group, e.g. if the first x has to be numeric, it would be
like '[0-9]-_____-_-___' .
For more specifi tests, this gets quite clumsy, but at least it works...
WM_JUSTMY2CENTS
April 2, 2009 at 7:32 am
Thanks both for the quick response, your answers were exactly what I was looking for!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply