finding non alphanumeric characters in a string

[sandra64]

Dear all

We have data in a field within a table that has non alphanumeric characters in it. We want to be able to find these fields that contain such characters. How do we do this?

We've looked up ANSI but to no avail as there are no examples on how to use this. Plus we can't find a table for ANSI codes. Have anyone done this before? and can you give us a shove in the right direction?

much appreciated
Sandra

 

[Golom]

You probably need a UDF (User Defined Function) to identify non-AlphaNumeric Characters. Question is, what information are you interested in seeing? The characters? The number of such characters? A simple "Yes/No" that they exist? The field value with non-alphanumerics removed?"

 

[sandra64]

Golom

What we want to see is the client number which is the key field on the record and the actual fields with the non-alphanumeric characters in.
We did think of doing this in a while loop checking each character of the string but as we've got thousands of records we're not sure how long it would take. We'll most probably have to do it that way.

cheers
Sandra

 

[r937]

select * from yourtable
where somecol like '%[^a-zA-Z0-9]%'

... or something like that

rudy

http://r937.com/

SQL Consulting

 

[SQLSister]

http://www.asciitable.com/

has the ascii codes

Looks like the first 32 are non-printing charaters.

YOu could use the Replace Function and the ASCII() function to create a way to strip out these characters in an update statment. No need to loop at all.

 

[sandra64]

thank you r937 it works.......

Sandra