ASCII syntax help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ASCII syntax help

Author

Topic

rfleming
Starting Member

9 Posts

Posted - 2005-10-11 : 10:51:22

I have used the following stmt to find field that have certain ascii characters i don't want.

select ID, COMPANY from Name where patindex('%'+char(13)+'%',COMPANY) > 0

What would be the syntax, if any, to search a field for ALL possible ascii characters and return the field and it's ascii code/character(s)?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-11 : 11:06:52

So if you have a column called [company] in a table with 2 rows like this:


Company
-----------
Sears
ABC Inc.

you want output like this?


char   code
-------------
S       83
e       101
a       97
r       114
s       blah
A       blah
B
C

I
n
c
.

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 11:10:53

Or if you are looking for only rows with "duff" characters

select ID, COMPANY
from Name
where COMPANY LIKE '%[^ -~]%'

i.e. contains characters which are NOT between CHAR(32) and CHAR(126)

Kristen

rfleming
Starting Member

9 Posts

Posted - 2005-10-11 : 11:19:21

TG, close but more like-

Company code
--------------------
Sears 13
ABC Inc 34

Kristen- excellent but i need to know what char code it returns.

rfleming
Starting Member

9 Posts

Posted - 2005-10-11 : 11:49:13

I answered my own question for once-

select ascii(right(country,1)) from Name

returned the appropriate ascii code for me to then be able to remove them.

Thanks to all.

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 13:44:46

Do you need to cover the situation where the "duff" character is in the middle of the field?

Kristen

rfleming
Starting Member

9 Posts

Posted - 2005-10-11 : 13:49:21

oooops, yep. I would need to cover all situations wherever it appears.

beginning, middle or end.

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 14:00:48

Well ... my query should give you the rows where there is a problem, then I would join those to a tally table to find the offending individual characters

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-11 : 19:15:41

I've seen a large nested replace statement before which worked supprisingly well. The difficult part is determining all the characters you want to replace. The statement had about 30 nested REPLACEs and went through about 300,000 rows in just a few seconds. If you're interested, I could dig it out and post it when I get back to the office tomorrow.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources