Display control code, or non-printable character within string

I have found several records that look to contain trailing spaces after a number, but I cannot query them based on the string containing a space so I believe the records have some sort of control code or no-printable character in them.  I can select the records based on them containing something other than a number or letter, but I want to be able to see what the record really contains.  

Here is how I found the records:

select '('||month||')'
from birthdates
where nvl(length(translate(month,'a1234567890','a')),-1) > 0
/

The records returned look like this:
(1  )
(2  )
(9  )
etc.

They are not spaces that follow the numbers but I cannot find out what they are.  Basically I need to update the records to just show the month number.  The data is mostly accurate  (containing just the number with nothing following) but there are 100's that appear like the above.
bretthonn13Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Typo:

update birthdates
set month = regexp_replace(month,'[^0-9]+')
where regexp_like(month,'[^0-9]+')
and city = 182
0
 
slightwv (䄆 Netminder) Commented:
>>but I want to be able to see what the record really contains

use DUMP to show the hex codes:
select DUMP(month) ...

>>update the records to just show the month number

update birthdates set months=regexp_replace(month,[^0-9]);
0
 
slightwv (䄆 Netminder) Commented:
Had a typo above (missed a +):
update birthdates set months=regexp_replace(month,[^0-9]+)


Can add a where clause if you wish:
update birthdates set months=regexp_replace(month,[^0-9]+) where regexp_like(month,'[^0-9]+);
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bretthonn13Author Commented:
Thanks!

I took a look and this is what it returned:
month      dump(month)
5               Typ=1 Len=2: 53,13
5               Typ=1 Len=2: 53,13
6               Typ=1 Len=2: 54,13
6               Typ=1 Len=2: 54,13

So what is this telling me?
0
 
slightwv (䄆 Netminder) Commented:
The 13 is a Carriage Return.

www.asciitable.com
0
 
bretthonn13Author Commented:
So in the future if I knew I had a bunch of "13's" or Carriage Returns in some data, can you simply search or select those records containing only Carriage REturns?
0
 
slightwv (䄆 Netminder) Commented:
>> can you simply search or select those records containing only Carriage REturns?

Sure:
select month from birthdates where instr(month,chr(13)) > 0;
0
 
bretthonn13Author Commented:
I think it's going to work but I keep getting "error at line 2 missing expression"

update birthdates
set month = regexp_replace(month,[^0-9]+)
where regexp_like(month,[^0-9]+)
and city = 182
0
 
bretthonn13Author Commented:
Super fast and exactly what I was looking for!
0
 
slightwv (䄆 Netminder) Commented:
Glad to help and sorry for the syntax error.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.