July 17, 2008 at 11:19 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:43 am
Try
WHERE ISNUMERIC(REPLACE (REPLACE (ex,'.','X'), '/','')) = 0
The REPLACE (ex,'.','X') just changes the . to a letter as otherwise ISNUMERIC wil be true.
The other replace function just removes the // and / from what to evaluate so that ISNUMERIC will evaluate to true fro the statement.
July 18, 2008 at 12:01 pm
I'm assuming your table has a PK, probably an ID. Here's something that will work:
;with
Numbers (Number) as
(select row_number() over (order by object_id)
from sys.all_objects),
SplitString (ID, SubChar) as
(select ID, substring(att, number, 1)
from dbo.Table1
inner join Numbers
on Number <= len(att))
select distinct ID
from SplitString
where subchar not like '[0-9/]'
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply