Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Selecting special characters

Posted on 2004-08-23
3
Medium Priority
?
704 Views
Last Modified: 2008-01-16
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
Comment
Question by:ejr19592004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11877456
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
 

Author Comment

by:ejr19592004
ID: 11877838
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 225 total points
ID: 11877943
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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