Is there any way to filter by the output format in sql server.

Below is my output :


AB-123456-99 Checker

123456-10000-00 2016

111119-20000-00 2016

How can I get the expected output as below,

the query should exclude all the column values which are in the format

123456-10000-00 -- xxxxxx-xxxxx-xx

If the column output has this format that value should be excluded from results.

and my final output should be as below :


AB-123456-99 Checker

Please help if there is any way to get this.

  • Your example in contradictory. 123456-10000-00 2016 does not have a - between 00 and 2016. Then you said exclude any with format 123456-10000-00 -- xxxxxx-xxxxx-xx. Is all your output will have AB (or any 2 English alphabets) at the beginning and Checker at the end? Does case matter? – SqlWorldWide May 25 '17 at 11:19
  • Add'l questions: Are you looking to eliminate rows where the first column is like xxxxxx-xxxxx-xx, rows where any column matches said pattern, or leave rows in place, blanking out columns that match the pattern. I'm assuming the first option, but your question as stated isn't clear. In your examples, are 2016 and Checker a second column, or part of the first column? If part of the first column, then are you looking for columns that start with the pattern? that contain the pattern? Also - DDL for the table(s), and the current query would help. – RDFozz May 25 '17 at 16:39

Assuming that your output is all one column, I would go with the conditional clause below. If you output is more than one column, then you can make the adjustments accordingly.

(Column1 VARCHAR(50))

        ( Column1 )
VALUES  ( 'AB-123456-99 Checker')
    ,('123456-10000-00 2016')
    ,('111119-20000-00 2016')

SELECT t.Column1 FROM @table AS t
WHERE t.Column1 NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%'

So, you would essentially replace the x with [0-9] for numeric. If it was alpha, you would replace the x with [a-z] for case insensitive collations and [A-Za-z] for case sensitive collations. If it's alpha or numeric, it would be [0-9a-z] or [0-9A-Za-z]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.