1

I want to get only those rows that contain ONLY certain characters in a column.
Let's say the column name is DATA.

I want to get all rows where in DATA are ONLY (must have all three conditions!):

  • Numeric characters (1 2 3 4 5 6 7 8 9 0)
  • Dash (-)
  • Comma (,)

For instance:

Value "10,20,20-30,30" IS OK

Value "10,20A,20-30,30Z" IS NOT OK

Value "30" IS NOT OK

Value "AAAA" IS NOT OK

Value "30-" IS NOT OK

Value "30," IS NOT OK

Value "-," IS NOT OK

no9
  • 6,424
  • 25
  • 76
  • 115

4 Answers4

4

Try patindex:

select * from(
    select '10,20,20-30,30' txt union
    select '10,20,20-30,40' txt union
    select '10,20A,20-30,30Z' txt
)x
where patindex('%[^0-9,-]%', txt)=0

For you table, try like:

select 
    DATA
from 
    YourTable
where 
    patindex('%[^0-9,-]%', DATA)=0

As per your new edited question, the query should be like:

select 
    DATA
from 
    YourTable
where 
    PATINDEX('%[^0-9,-]%', DATA)=0 and
    PATINDEX('%[0-9]%', LEFT(DATA, 1))=1 and
    PATINDEX('%[0-9]%', RIGHT(DATA, 1))=1 and
    PATINDEX('%[,-][-,]%', DATA)=0
TechDo
  • 18,398
  • 3
  • 51
  • 64
2

Edit: Your question was edited, so this answer is no longer correct. I won't bother updating it since someone else already has updated theirs. This answer does not fulfil the condition that all three character types must be found.

You can use a LIKE expression for this, although it's slightly convoluted:

where data not like '%[^0123456789,!-]%' escape '!'

Explanation:

[^...] matches any character that is not in the ... part. % matches any number (including zero) of any character. So [^0123456789-,] is the set of characters that you want to disallow.

However: - is a special character inside of [], so we must escape it, which we do by using an escape character, and I've chosen !.

So, you match rows that do not contain (not like) any character that is not in your disallowed set.

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
0

Use option with PATINDEX and LIKE logic operator

SELECT *
FROM dbo.test70
WHERE PATINDEX('%[A-Z]%', DATA) = 0
  AND PATINDEX('%[0-9]%', DATA) > 0
  AND DATA LIKE '%-%'
  AND DATA LIKE '%,%'

Demo on SQLFiddle

Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44
0

As already mentioned u can use a LIKE expression but it will only work with some minor modifications, otherwise too many rows will be filtered out.

SELECT * FROM X WHERE T NOT LIKE '%[^0-9!-,]%' ESCAPE '!'

see working example here: http://sqlfiddle.com/#!3/474f5/6

edit:

to meet all 3 conditions:

    SELECT * 
    FROM X
    WHERE T LIKE '%[0-9]%'
      AND T LIKE '%-%'
      AND T LIKE '%,%'

see: http://sqlfiddle.com/#!3/86328/1

Maybe not the most beautiful but a working solution.

Felix Bayer
  • 363
  • 2
  • 11
  • hey! Its working but ... i want only the records that include only numbers, dashes and commas. All three conditions must be met! – no9 Apr 03 '13 at 09:25
  • Obviously i missunderstood your question and/or can't see the point why all three conditions must be met but that could be done like this (maybe there's also a less ugly solution ;-)) `SELECT * FROM X WHERE T LIKE '%[0-9]%' AND T LIKE '%[!-]%' ESCAPE '!' AND T LIKE '%[,]%'` – Felix Bayer Apr 03 '13 at 09:43