Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Avoid a record that has first character as number in a column Expand / Collapse
Author
Message
Posted Friday, February 04, 2011 12:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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
Posted Friday, February 04, 2011 12:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:50 PM
Points: 1,663, Visits: 5,230
Best not to do it with IN.

Try using LIKE

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


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1058973
    Posted Friday, February 04, 2011 12:53 PM


    SSCoach

    SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

    Group: General Forum Members
    Last Login: Yesterday @ 10:04 AM
    Points: 15,442, Visits: 9,590
    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
    Posted Friday, February 04, 2011 12:53 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 4:50 PM
    Points: 1,663, Visits: 5,230
    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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1058976
    Posted Friday, February 04, 2011 1:06 PM


    SSCoach

    SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

    Group: General Forum Members
    Last Login: Yesterday @ 10:04 AM
    Points: 15,442, Visits: 9,590
    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
    Posted Friday, February 04, 2011 4:57 PM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Tuesday, January 28, 2014 3:52 PM
    Points: 377, Visits: 685
    Thank you. That works.
    Post #1059089
    Posted Sunday, February 06, 2011 10:27 PM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Today @ 4:33 AM
    Points: 1,001, Visits: 1,043
    this may help if you Ignore only first number char from Str

    select * from Table where isnumeric(left(column_name,1))=0
    Post #1059317
    Posted Sunday, February 06, 2011 10:58 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 5:22 PM
    Points: 36,016, Visits: 30,308
    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." -- 04 August 2013
    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1059330
    Posted Monday, February 07, 2011 7:07 AM


    SSCoach

    SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

    Group: General Forum Members
    Last Login: Yesterday @ 10:04 AM
    Points: 15,442, Visits: 9,590
    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 »

    Add to briefcase

    Permissions Expand / Collapse