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

Selecting special characters

Hello,

I have a number of columns in my dataset one/or more of which I believe might have an embedded special character such as a CR or LF. I have tried using the following in my query:

patindex('%' + char(13) + '%' , texfield) > 0 to no avail. I have also tried charindex with the same result.

I know that the special character is there because when I export the data out to text somewhere along the way the records gets truncated into two.

I would like to be able to locate this special character and strip it out. Any suggestions.

My next step is to write a VB program to go through the dataset.

Thanks
Eddie
0
ejr19592004
Asked:
ejr19592004
  • 2
1 Solution
 
Anthony PerkinsCommented:
Try something like this:
PATINDEX('%[^0-9][^A-Z][^a-z]%', textfield) > 0

To give you non-alpha-numeric characters.

Or to give you non-printable characters, something like:
PATINDEX('%[^ - Ç]%', textfield) > 0
0
 
ejr19592004Author Commented:
ACPerkins,

Thanks for your quick response. Is the line
PATINDEX('%[^0-9][^A-Z][^a-z]%', textfield) > 0
meant to look for anything that is not 0-9 A-Z and a-z ?

ejr19592004
0
 
Anthony PerkinsCommented:
Yes.  Unfortunately it gets more complicated as I have discovered with other ranges.  Please disregard the second "solution" it is incorrect. For some reason that I have not managed to ascertain, it simply does not function (at least for me) with anything beyond the trivial non alphanumeric characters.

You may have to resort to using a cursor and cycle through all the rows. Looking for non-printable characters. Ugh!

Hopefully, someone will step in.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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