function to identify the special characters in a table

Question

Post reply

function to identify the special characters in a table

nabajyoti.b

SSCrazy

Points: 2177
More actions
March 25, 2009 at 5:20 am

#132293

can we have a function to identify the special characters in a table?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

Which special characters are you looking for? Please read following link and post sample tables and data.

http://www.sqlservercentral.com/articles/Best+Practices/61537/ [/url]

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

of course...but it depends on what are "special characters" for you;

do you mean where any character is not alphanumeric(or space) like this?

select * from gmact where actname like '%[^a-z,A-Z,0-9 ]%'

or are you looking for hi ascii codes? what is it specifically?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 3

sure, but what is "special characters" for you?

anything not a space and alphanumeric, for example? or are you after specific chars, like hi ascii characters?

here's a simple example

select * from YOURTABLE where SOMECOLUMN like '%[^a-z,A-Z,0-9 ]%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

If it does not need to be a function per se, then why reinvent the wheel ...

sp_validname

http://msdn.microsoft.com/en-us/library/ms189525.aspx

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

SinghS SSC Journeyman Points: 85 More actions · Answer 5

I am struggling with same issue. I want to identify special characters at varchar column so that I can replace it with some other character.

Example: “Hello World”

Space between “Hello” and “World” is some special character; I want to know what that character is so that I can replace it with some other character.

Like:

UPDATE dbTABLE set tblColA=rtrim(ltrim(REPLACE(tblColA,char(10),'XX'))) where (tblColA like '%' + char(10) + '%')

Thank You

arun.sas SSChampion Points: 11831 More actions · Answer 6

Hi Singh,

You need to update the space to ‘XX’

Then

UPDATE dbTABLE

set tblColA= ‘XX’

where tblColA like ' ' –space(not a singe code,with one space)

ARUN SAS

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 7

SinghS (3/27/2009)
I am struggling with same issue. I want to identify special characters at varchar column so that I can replace it with some other character.
Example: “Hello World”
Space between “Hello” and “World” is some special character; I want to know what that character is so that I can replace it with some other character.
Like:
UPDATE dbTABLE set tblColA=rtrim(ltrim(REPLACE(tblColA,char(10),'XX'))) where (tblColA like '%' + char(10) + '%')
Thank You

I suspect that you mean you want to find and replace more than just one special character at a time. There're lots of ways to do it but, to determine the best way for your situation, you need to help us help you. We need the Create statement for a test table and some "readily consumable" test data. Please see the link in my signature below for what I mean by all of that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 8

I know this was posted ages ago, but I just came across a scenario that requires me to replace such values and I would like to be able to do it using a single update statement such as:

update TABLE

set FIELD = Replace(FIELD,,'%[^a-z,A-Z,0-9 ]%','')

I'm trying to remove all those characters from my column values. How can I do this?

Regards,

John