[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

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

Posted on 2014-02-25
Medium Priority
387 Views
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)
----------

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.
0
Question by:bretthonn13
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 35

Expert Comment

ID: 39886475
SELECT * FORM TAble1 WHERE Field1 LIKE '%13'
0

LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 39886490
decimal character 13 is a carriage return rather than searching on the dump output
just search for that character

select '('||website||')', dump(website)
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

LVL 32

Expert Comment

ID: 39886532
where instr(website,chr(13)) = length(website);
0

LVL 74

Expert Comment

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

same idea as LIKE '%' ||13 but more expensive to execute
0

LVL 32

Expert Comment

ID: 39886564
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

Author Comment

ID: 39886798
decimal character 13 is a carriage return rather than searching on the dump output
just search for that character

select '('||website||')', dump(website)
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

LVL 74

Expert Comment

ID: 39886869
doing the update is easy, but only you can decide if it's ok to do so or not.

set website = rtrim(website,chr(13))
where website like '%' || chr(13)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
###### Suggested Courses
Course of the Month12 days, 21 hours left to enroll