Avoid a record that has first character as number in a column

Question

Post reply

Avoid a record that has first character as number in a column

ramadesai108

SSChampion

Points: 10609
More actions
February 4, 2011 at 12:48 pm

#246228

Hi,
I want to avoid a record that has first letter as number in a column.
This is my code:
where left(firstname,1) not in('','"','#','.','%','$','-','_','(',')','*,',)
along with the other not ins, how do I add that the first character should not be a number.
Thanks.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Best not to do it with IN.

Try using LIKE

where firstname not like '[0-9]%'

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

GSquared SSC Guru Points: 260824 More actions · Answer 2

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

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);

GSquared SSC Guru Points: 260824 More actions · Answer 4

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

ramadesai108 SSChampion Points: 10609 More actions · Answer 5

ramadesai108

SSChampion

Points: 10609

February 4, 2011 at 4:57 pm

#1282761

Thank you. That works.

srikant maurya Hall of Fame Points: 3293 More actions · Answer 6

this may help if you Ignore only first number char from Str

select * from Table where isnumeric(left(column_name,1))=0

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

srikant maurya (2/6/2011)
this may help if you Ignore only first number char from Str
select * 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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 8

srikant maurya (2/6/2011)
this may help if you Ignore only first number char from Str
select * 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