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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Avoid a record that has first character as...

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

Rate Topic

Display Mode

Topic Options

Author

Message

ramadesai108

Posted Friday, February 04, 2011 12:48 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

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.

Post #1058970

mister.magoo

Posted Friday, February 04, 2011 12:51 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 1,337, Visits: 4,025

Best not to do it with IN.

Try using LIKE

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

MM

Post #1058973

GSquared

Posted Friday, February 04, 2011 12:53 PM

SSCoach

Group: General Forum Members
Last Login: Monday, June 17, 2013 1:45 PM
Points: 15,442, Visits: 9,572

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

Post #1058975

mister.magoo

Posted Friday, February 04, 2011 12:53 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 1,337, Visits: 4,025

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 Smile

Post #1058976

GSquared

Posted Friday, February 04, 2011 1:06 PM

SSCoach

Group: General Forum Members
Last Login: Monday, June 17, 2013 1:45 PM
Points: 15,442, Visits: 9,572

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

Post #1058985

ramadesai108

Posted Friday, February 04, 2011 4:57 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

Thank you. That works.

Post #1059089

srikant maurya

Posted Sunday, February 06, 2011 10:27 PM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:32 AM
Points: 935, Visits: 1,003

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

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

Post #1059317

Jeff Moden

Posted Sunday, February 06, 2011 10:58 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:54 PM
Points: 33,113, Visits: 27,041

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1059330

GSquared

Posted Monday, February 07, 2011 7:07 AM

SSCoach

Group: General Forum Members
Last Login: Monday, June 17, 2013 1:45 PM
Points: 15,442, Visits: 9,572

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

Post #1059517

« Prev Topic | Next Topic »

Permissions