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

bretthonn13
bretthonn13 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT * FORM TAble1 WHERE Field1 LIKE '%13'
Most Valuable Expert 2011
Top Expert 2012
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
awking00Information Technology Specialist

Commented:
select * from business
where instr(website,chr(13)) = length(website);
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012

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

same idea as LIKE '%' ||13 but more expensive to execute
awking00Information Technology Specialist

Commented:
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 :-)

Author

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?
Most Valuable Expert 2011
Top Expert 2012

Commented:
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)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial