• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

Finding Non-standard characters in a SQL Server table

A bug in one of our import processes allowed non-standard characters (like Ö]¡]ÐÉ]¿)  to get imported into one of our tables.  The characters can appear at the end of a string of standard characters or they can appear in the middle of the string.   There may be only 1 non-standard character in the field, or multiple non-standard characters.  I'm not really sure which non-standard characters were included, so I can't just search for a particular character.  I need to find anything that's not A-Z or 0-9, and not a comma, apostrophe, number sign, bracket, parentheses, or other standard symbol in American English.

I need to find the records that are affected.  Is there a query I can run that will detect these non-standard characters?  I don't want to replace them yet, just to identify the affected records.
0
n f
Asked:
n f
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
select * from yourtable
where yourfield like '%[^A-Za-z0-9]%'

Open in new window

0
 
n fAuthor Commented:
I tried angelll's code, but it returned records with no special characters as well as records with special characters.  Does '%[^A-Za-z0-9]%' include spaces?
0
 
n fAuthor Commented:
An example of some of the fields returned by angelll's code (there were many, many more)::

VALDOSTA�
ST JOHN'S
MT PEARL N
GRAND-M�RE QC G9T 2
TROIS-RIVI�RESQC G8T
NEW ALEXANDRIA
N HUNTINGDON
MT PLEASANT

Only
VALDOSTA�
GRAND-M�RE QC G9T 2
and
TROIS-RIVI�RESQC G8T
really have the special characters.

These three examples all have special characters "�".  I know those characters are in my table, but I need to find out if any others exist.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if spaces should be allowed, add the space in the expression.
if you need any other character, just add it.

select * from yourtable
where yourfield like '%[^A-Za-z0-9 ]%'

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for example, single quotes:
select * from yourtable
where yourfield like '%[^A-Za-z0-9 '']%'

Open in new window

0
 
n fAuthor Commented:
That did it!  Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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