Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: ISNUMERIC() in LIKE statement

    Hi

    Here's the problem:

    I need to search a postcode database by the first one or two letters.

    Problems occur for example when i want to search north London postcodes (N) when using:

    postcode LIKE @postcode + '%'

    As this picks up everything beginning with N, eg, NG for Nottingham, or NE for Newcastle. So i need a like statement which searches for the first one or two digits followed by a number!

    I've found the ISNUMERIC() function but not sure what the best way to use it with the like statement - or even if there is a better way altogether - can you use regular expressions in MSSQL?

    thanks
    Last edited by mattock; 01-10-07 at 07:53.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SET @postcode = 'N[0-9]%'
    ????
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    yup, that works, i ended up using:

    ...
    WHERE postcode LIKE @region + '[0-9]%'

    I know [0-9] certainly looks like a regex, is it(?) just a simple case of putting in your regex into a statement like that? Do you know of anywhere there's a list of regex operator types i can use in MSSQL if this is the case?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's not true regex

    see LIKE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    thats spot on....i thought it was a bit weird having the wildcard (%) symbol - hadn't seen it before in any regex i've done.

    thanks again....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •