[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1374
  • Last Modified:

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

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.

-M
0
Rubicon2009
Asked:
Rubicon2009
3 Solutions
 
sshah254Commented:
Use this example to check it out - http://stackoverflow.com/questions/150977/perform-regex-replace-in-an-sql-query

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.

Ss
0
 
cojdevCommented:
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]%'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
close:


SELECT address FROM users where address LIKE '%^[A-Za-z]%'
0
 
SharathData EngineerCommented:
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?
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now