return records in sql server which has data in a particular format

Question

Hi Guys,

I have a SQL database table column which has data in this format "0000-00-0000"

for ex: "1234-98-2010"

For it has data in other formats as well.

I need to pick out all the records where the format is "0000-00-0000" irrespective of the data. Its the format which i am after

Please, can some one advise me on how i can pick these records in sql server 2005

Answer 1

Use the like operator with a pattern

Select * from TableA
Where ColumnA like '[0123456789][0123456789][0123456789][0123456789]-[0123456789][0123456789]-[0123456789][0123456789][0123456789][0123456789]'

Answer 2

;WITH T AS
(
SELECT '1234-98-2010' AS S UNION ALL SELECT 'foo'
)
SELECT S 
FROM T 
WHERE S LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'

Answer 3

Try:

 SELECT * FROM MyTable WHERE MyColumn LIKE '____-__-____'

The _ is a wildcard for one character.

Answer 4

SELECT * FROM sometable
WHERE c1 LIKE '____-__-____'