SELECT Records based on octal or decimal value at the end of a string

I need to select records from a table if they have a certain decimal value at the end of the string.  After an import we get many records that contain decimal value 13 as the final character.  I determined that based on doing a dump in PL/SQL.

select '('||website||')', dump(website)
from business
----------

I get the following:
(sunshinecavaliers.com ), Typ=1 Len=22:115,117,110,115,104,105,110,101,99,97,118,97,108,105,101,114,115,46,99,111,109,13

Now, that I know my problem records contain a decimal value of '13' I want to search my entire DB to find all records that contain the '13' at the end of the website field.
bretthonn13Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
decimal character 13 is a carriage return rather than searching on the dump output
just search for that character



select '('||website||')', dump(website)
from business
where website like '%' || chr(13)


note, looking for trailing characters is not normally going to be something you can use an index, so this query might take a while if you have many rows
0
 
YZlatCommented:
SELECT * FORM TAble1 WHERE Field1 LIKE '%13'
0
 
awking00Commented:
select * from business
where instr(website,chr(13)) = length(website);
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
sdstuberCommented:
>>>  instr(website,chr(13)) = length(website);

same idea as LIKE '%' ||13 but more expensive to execute
0
 
awking00Commented:
I agree and wouldn't have even posted my suggestion except I wrote it much earlier but never submitted it after I received a phone call and never even saw the other responses. I was going to withdraw it, but apparently cannot after a new comment has been submitted. You're just too fast, Sean :-)
0
 
bretthonn13Author Commented:
decimal character 13 is a carriage return rather than searching on the dump output
just search for that character



select '('||website||')', dump(website)
from business
where website like '%' || chr(13)


It works great.  Unfortunately I have far more records with problems than I thought.  I should be able to update those records and simply remove the chr(13) though right?
0
 
sdstuberCommented:
doing the update is easy, but only you can decide if it's ok to do so or not.

update business
set website = rtrim(website,chr(13))
where website like '%' || chr(13)
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.