Skip to Main Content
  • Questions

  • how to correctly identify the correct ASCII values for the special characters.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, han.

Asked: December 12, 2018 - 3:05 am UTC

Last updated: May 20, 2019 - 11:23 am UTC

Version: Oracle Sql devel0per 10

Viewed 10K+ times! This question is

You Asked

Hi

The column Text contains the apostrophe as special characters. Did a dump. you can see the results below. How do I correctly search for these special characters and then replace them with apostrophe ? You see the first row has the ASCII value 26 for the special character. The second row somehow seems to contain 239,191,189 for the special characters. Can you help me make sense out of this ? They should be 'CHILDREN's HOSPITAL' as end results ultimately. thank you

select text,-- dump(text), 
substr(text,8,3), dump(substr(text,8,3)) from 
(
select 'CHILDRENS HOSPITAL' as text from dual
union
select 'CHILDREN�S HOSPITAL' as text from dual
);

TEXT                 SUBSTR(TEXT,8,3) DUMP(SUBSTR(TEXT,8,3))
CHILDRENS HOSPITAL NS                 Typ=1 Len=3: 78,26,83
CHILDREN�S HOSPITAL N�S                 Typ=1 Len=5: 78,239,191,189,83




and Chris said...

You can use chr() to return the character for a given ASCII code. And ASCII to get the code for a given character.

So you can replace instances of chr ( 26 ) with an apostrophe or whatever:

select ascii ( ' ' ) space, ascii ( '''' ) apostrophe,
       replace (
        'Space to apostrophe', chr ( 32 ), chr ( 39 ) 
       ) str
from   dual;

SPACE   APOSTROPHE   STR                   
     32           39 Space'to'apostrophe

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to search for the special chars

han lambruschini, December 13, 2018 - 12:52 am UTC

Hello, thanks for the help.
I am thinking that to search for string containing any special characters, I want to do a dump on the string. Maybe I then can search the dumped ',' parsed ASCII codes for the special characters. If the dumped string containing any ASCII value > then 126 or < then 32, then I would know the string contains special characters. I then can report the result as such. what would be a good way to do this?
thanks
Chris Saxon
December 13, 2018 - 1:29 pm UTC

You can convert the text to rows, then substr the text to get the ascii value of the character in each position:

with str as (
  select '#a$%' s from dual
), rws as (
  select level x from dual
  connect by level <= 4
)
  select x, substr ( s, x, 1 ),
         ascii ( substr ( s, x, 1 ) ) aci
  from   rws 
  cross join str;

X   SUBSTR(S,X,1)   ACI   
  1 #                  35 
  2 a                  97 
  3 $                  36 
  4 %                  37

I'm Amazed

Jim, May 17, 2019 - 7:42 pm UTC

This query was exactly what I was looking for, it worked perfectly for my finding the chr(10) in my data and spewing out the whole string. It was something I wouldn't have been able to write and I'm definitely going add it to sql toolbox. After I understand it a bit better. A "with", a "conenct by level" and a "cross join" impressive. I gotta up my game :)
Chris Saxon
May 20, 2019 - 11:23 am UTC

Thanks :)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.