0

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

Below is my output :

SQLOutput

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 :

SQLOutput

AB-123456-99 Checker

Please help if there is any way to get this.

Improve this question
2
  • 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? Commented May 25, 2017 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. Commented May 25, 2017 at 16:39

1 Answer 1

Reset to default
0

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.

DECLARE @table TABLE
(Column1 VARCHAR(50))

INSERT INTO @table
        ( 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]

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.