TSQL : Find non a-z, A-Z, "-" caracters in a column

Posted on 2011-05-05
Last Modified: 2012-05-11
Hi !

I need to find a way to query my SQL 2008 database to find all caracters that are not a-z, A-Z and "-" in the column named "address".

The database collation is : Latin1_General_CI_AS

I've try to do "SELECT address FROM users where address LIKE '%é%' but this was not effective.

Question by:Rubicon2009
    LVL 9

    Accepted Solution

    Use this example to check it out -

    Essentially the loop should run for each character and replace it with "".  At the end you'll be left with all non alphabetic characters.

    If these are US addresses, then there would be an efficient way of parsing the data string.

    LVL 2

    Expert Comment

    Is this what you are looking for?

    SELECT address FROM users where address LIKE '%[^A-Za-z]%'

    The ^ is like Not

    This might work;
    SELECT address FROM users where address NOT LIKE '%[A-Za-z]%'
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]

    SELECT address FROM users where address LIKE '%^[A-Za-z]%'
    LVL 40

    Assisted Solution

    Do you want to extract all characters other than a-z, A-Z and "-" from address field or do you want a filter condition to filter the address column to exclude these values?

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now