Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Find rogue character (SQL)

    <P ID="edit" class=small>(Edited by Jezza on 05-Sep-06 12:12. To add additional comment)</P>I have a massive table within my CRM system that is connected to multiple back office legacy databases. As you are aware, the problem with legacy systems is that they have a tendency to have free text fields and with this allows the introduction of data input errors.

    Telephone numbers are the new issue as the CRM is pulling in old telephone STD from legacy and I want to clear it up once and for all. I have managed the majority of these but I am scratching my head for the SQL which will find records with non numeric content within the string, an example of this would be where a telephone number is expressed as :-


    (0123)4567890 or

    0123-4567890

    There are a pletherer of such examples with different characters, any idea

    Addition to original

    So if i use:

    SELECT vchPhoneNumber AS strRogue
    FROM Individual
    WHERE CONTAINS(*, ' ")" ')

    I get a freetext error message <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Jerry

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Find rogue character (SQL)

    WHERE (((InStr([vchPhoneNumber],')'))>0))

    will find records with a ) in them.
    Regards
    John



  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find rogue character (SQL)

    Thanks John

    I was just working on this and got the answer as you posted, I have:

    SELECT vchPhoneNumber AS strRogue
    FROM Individual
    WHERE (isnumeric(vchPhoneNumber) = 0) AND (vchPhoneNumber NOT LIKE '% %')

    This selects everything which has a rogue character and no spaces

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Find rogue character (SQL)

    what about 0123.456789 ?

    This rogue character still makes it numeric.
    Regards
    John



  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find rogue character (SQL)

    Good point <img src=/S/pun.gif border=0 alt=pun width=22 height=18>

    I shall have a quick run on that.

    I am going totally mad today, I need to see if there are spaces in the numbers but not if the field has just one space in it and no number <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Jerry

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find rogue character (SQL)

    Just realised...that is fine in Access but not SQL Server <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Jerry

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find rogue character (SQL)

    OK Jezza

    Calm down , it appars a day when you answer your own questions <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    SELECT vchPhoneNumber AS strRogue
    FROM Individual
    WHERE (isnumeric(vchPhoneNumber) = 0) AND (LEN(vchPhoneNumber) > 1)

    I am doing LEN(vchPhoneNumber) > 1 for this particular query as there are just single spaces in the fields... a hang back from legacy
    Jerry

Posting Permissions

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