use 'like' command to filter rows with alphanumeric characters

Question

Post reply

use 'like' command to filter rows with alphanumeric characters

shiwani2002sg

Ten Centuries

Points: 1037
More actions
July 17, 2008 at 11:12 pm

#129929

i have a table say 'table1' with one attribute say 'att'. now it have values as
att
------
123//4
143
2343
12.34
23*5
23a34
456=
345//4
3434//4/5
now i want a query which should display all rows which have values ,which contain any other character than 0-9 or / or //
i.e
the querry must display
att
------
12.34
23*5
23a34
456=
its urgetnt

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7216 More actions · Answer 1

select ...

from t

where col like '%[^0-9/]%'

Should I think do the trick.

This will give you all rows where any single character is not in the range 0-9 or /

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 2

Hi Shiwani,

Try this

SELECT * From

(

SELECT '123//4' [Value] UNION

SELECT '143' UNION

SELECT '2343' UNION

SELECT '12.34' UNION

SELECT '23*5' UNION

SELECT '23a34' UNION

SELECT '456=' UNION

SELECT '345//4' UNION

SELECT '3434//4/5'

) AS Temp

WHERE [Value] LIKE '%[^0-9/]%'

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 3

Ohh sorry! Mike already posted it.

Thats true.

Cheers,
Hari
Tips & Tricks for SQL BI Developers

shiwani2002sg Ten Centuries Points: 1037 More actions · Answer 4

thanks so much mike, it worked. now plz elaborate the query from accepting \ to some other char too

say 0-9, \ , a , d , e

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 5

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. Seefollowing examples of using the LIKE keyword and the [ ] wildcard characters.

Symbol Meaning

LIKE '5[%]' 5%

LIKE '[_]n' _n

LIKE '[a-cdf]' a, b, c, d, or f

LIKE '[-acdf]' -, a, c, d, or f

LIKE '[ [ ]' [

LIKE ']' ]

LIKE 'abc[_]d%' abc_d and abc_de

LIKE 'abc[def]' abcd, abce, and abcf

Cheers,
Hari
Tips & Tricks for SQL BI Developers

shiwani2002sg Ten Centuries Points: 1037 More actions · Answer 6

hay hari ,

Thanks

i have sql server 2005 bible

bt u didnt get my problem

i want query to search 123.56a or 125.7?

shiwani2002sg Ten Centuries Points: 1037 More actions · Answer 7

thanks all , i made it. i used 'and' as

field_no like '%[^0-9/]%' and field_no not like N'%???%'

and it worked . thanks for all help

use 'like' command to filter rows with alphanumeric characters

Cookies on SQLServerCentral