Q
Problem solve Get help with specific problems with your technologies, process and projects.

Determining what characters are in a column

I have a table that was converted from another system:

 org_id		char(7),
 org_abbrev		varchar2(31)
 
I need to display the two columns using DBMS_OUTPUT.PUT_LINE. I use:
DBMS_OUTPUT.PUT_LINE(RPAD(org_id,7)||RPAD(org_abbrev,31));
But because there are characters in org_abbev other than NULL or blanks, the column is not filled to 31 characters. When I do LENGTH(org_abbrev), it shows 13, however if I select that same org_abbrev I can not see what characters are there.
 
ORG_ID  LENGTH(ORG_ABBREV)
 ------- ------------------
 pshidy                  13
 acicba
 
 ORG_ID  ORG_ABBREV
 ------- -------------------------------
 pshidy
 acicba
 
Is there someway to determine what characters are in a column?

It may be that you have non-printable characters in that column, or even blanks, tabs, etc. To "see" these characters, you'll want to show the corresponding ASCII code for these characters. Luckily, the DUMP function will show this for us:

ORA9I SQL> select ename,dump(ename) as dump_name from
emp;

ENAME      DUMP_NAME
---------- ----------------------------------------
SMITH      Typ=1 Len=5: 83,77,73,84,72
ALLEN      Typ=1 Len=5: 65,76,76,69,78
WARD       Typ=1 Len=4: 87,65,82,68
JONES      Typ=1 Len=5: 74,79,78,69,83
MARTIN     Typ=1 Len=6: 77,65,82,84,73,78
BLAKE      Typ=1 Len=5: 66,76,65,75,69
CLARK      Typ=1 Len=5: 67,76,65,82,75
SCOTT      Typ=1 Len=5: 83,67,79,84,84
KING       Typ=1 Len=4: 75,73,78,71
ADAMS      Typ=1 Len=5: 65,68,65,77,83
JAMES      Typ=1 Len=5: 74,65,77,69,83
FORD       Typ=1 Len=4: 70,79,82,68

12 rows selected.
Here, you can see the employee names. The DUMP command shows the length of each string followed by the ASCII codes which make up that string. Do this for your data, consult an ASCII chart, and you'll now instantly what your data contains. Even if you can't see it!

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close