This should give you what you need more efficiently that the current code:
where firstname NOT LIKE '["#.%$[-]_()*0-9]%'
The square-brackets give the Like operator a range of characters to operate on.
What you might want to do, instead of coding by exception, is:
where firstname LIKE '[A-Z]%'
That will find names that start with a letter, instead of excluding names from a list of specific characters.
- 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
although actually it may be better to do this:
where firstname LIKE '[!-/:-ÿ]%'
which is the set of printable ascii characters excluding 0-9
edit: when i said better i was referring to my previous post
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
I'd turn this:
where firstname LIKE '[!-/:-ÿ]%'
into:
where firstname LIKE '[^0-9]%'
The carat (up-arrow) means "excluding" in there. It's more clear that the exact thing you want to do is exclude 0-9, rather than an inclusive list of everything else.
- 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
Thank you. That works.
this may help if you Ignore only first number char from Str
select * from Table where isnumeric(left(column_name,1))=0
srikant maurya (2/6/2011)
this may help if you Ignore only first number char from Strselect * from Table where isnumeric(left(column_name,1))=0
Oh, be careful now. IsNumeric shouldn't be used as a digit test. Please see the following article on that, please...
http://www.sqlservercentral.com/articles/IsNumeric/71512/
--Jeff Moden
Change is inevitable... Change for the better is not.
srikant maurya (2/6/2011)
this may help if you Ignore only first number char from Strselect * from Table where isnumeric(left(column_name,1))=0
Besides not being a sure thing, that will also bypass indexes on that column.
- 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 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply