Solved

Finding Non-standard characters in a SQL Server table

Posted on 2009-04-02
6
283 Views
Last Modified: 2012-05-06
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
Comment
Question by:n f
  • 3
  • 3
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
this should do:
select * from yourtable
where yourfield like '%[^A-Za-z0-9]%'

Open in new window

0
 
LVL 1

Author Comment

by:n f
Comment Utility
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
 
LVL 1

Author Comment

by:n f
Comment Utility
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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
for example, single quotes:
select * from yourtable

where yourfield like '%[^A-Za-z0-9 '']%'

Open in new window

0
 
LVL 1

Author Closing Comment

by:n f
Comment Utility
That did it!  Thanks!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

9 Experts available now in Live!

Get 1:1 Help Now