• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2450
  • Last Modified:

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.
0
bretthonn13
Asked:
bretthonn13
  • 6
  • 4
2 Solutions
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
slightwv (䄆 Netminder) Commented:
Typo:

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now