July 17, 2008 at 11:12 pm
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
July 18, 2008 at 1:06 am
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 /
July 18, 2008 at 1:14 am
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/]%'
July 18, 2008 at 1:15 am
July 18, 2008 at 1:27 am
thanks so much mike, it worked. now plz elaborate the query from accepting \ to some other char too
say 0-9, \ , a , d , e
July 18, 2008 at 1:40 am
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
July 18, 2008 at 1:45 am
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?
July 18, 2008 at 1:52 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply