CODEWITHSUNDEEP
databaseoracle-database
Kumar Patil
Kumar Patil

Reputation: 21

How to see the stored char ascii value in SQL

I am trying to see the stored char ASCII value in memory but it will only show the first char ASCII. I want to complete char structure stored in memory.

For example, column name char(10)

and I am passing "abc".

It should display the int array as follows:

67 68 69 32 32 32 32 32 32 32

Here 32 stands for "Space"

Upvotes: 1

Views: 1657

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

If you just want to see the stored bytes then use dump(), which will show you in decimal or hexadecimal or even octal if you prefer. Bob's method to extract just the bytes from that output is useful, and you could optionally replace the commas with spaces if that's important.

If you did want to see the hex values you could also do:

select name,
  cast(utl_raw.cast_to_raw(name) as varchar2(20)) as ascii
from your_table;

NAME       ASCII                         
---------- ------------------------------
abc        61626320202020202020

And as an alternative to get the decimal values, you could use a hierarchical query:

select name,
  listagg(ascii(substr(name, level, 1)), ' ') within group (order by level) as ascii
from your_table
connect by level <= length(name)
group by name;

NAME       ASCII                         
---------- ------------------------------
abc        97 98 99 32 32 32 32 32 32 32

though that gets a bit messy with multiple rows to deal with, and is doing quite a bit of work compared to simply dumping the value.

A slight variation on Bob's just to get exactly the format you showed (which may not actually matter of course):

select name,
  dump(name) as dumped,
  trim(regexp_replace(dump(name), '((.*:)|,)', ' ')) as ascii
from your_table;

NAME       DUMPED                                        ASCII                         
---------- --------------------------------------------- ------------------------------
abc        Typ=96 Len=10: 97,98,99,32,32,32,32,32,32,32  97 98 99 32 32 32 32 32 32 32

(It isn't really ASCII unless your character set is US7ASCII; it's the decimal representation in the database character set... but that distinction is usually ignored. These will all give you values (> 127) for non-ASCII characters too though.)

Upvotes: 4

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

One way to do this is to DUMP the string, then grab the list of byte codes from the output:

SELECT SUBSTR(REGEXP_SUBSTR(DUMP('a b c d'), ':.*'), 3) FROM DUAL;

In your case you'd want to do something like

SELECT SUBSTR(REGEXP_SUBSTR(DUMP(YOUR_COLUMN_NAME), ':.*'), 3)
  FROM YOUR_TABLE

SQLFiddle here

Best of luck.

Upvotes: 3

Related Questions