bretthonn13
asked on
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 ,'a1234567 890','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.
Here is how I found the records:
select '('||month||')'
from birthdates
where nvl(length(translate(month
/
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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?
>> can you simply search or select those records containing only Carriage REturns?
Sure:
select month from birthdates where instr(month,chr(13)) > 0;
Sure:
select month from birthdates where instr(month,chr(13)) > 0;
ASKER
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
update birthdates
set month = regexp_replace(month,[^0-9
where regexp_like(month,[^0-9]+)
and city = 182
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Super fast and exactly what I was looking for!
Glad to help and sorry for the syntax error.
update birthdates set months=regexp_replace(mont
Can add a where clause if you wish:
update birthdates set months=regexp_replace(mont