File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes String funciton in oracle database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "String funciton in oracle database" Watch "String funciton in oracle database" New topic
Author

String funciton in oracle database

Meghna Bhardwaj
Ranch Hand

Joined: Jun 08, 2007
Posts: 109
Hi All,

I have a column in my Oracle 10g database with Countries, e.g

Albania
Albania-Cellular
Albania-District1
Albania-Xyz

and so on, I just want to select this column but with only the String preceeding the '-', e.g my select should return only

Albania for the above - basically truncate everything following the hyphen.

I looked up the rtrim function in Oracle

select rtrim(country,'-') from table

however this does not work as it removes from the very right of the string, i also tried using wildcard to have hyphen followed by any character and this does not work either.

select rtrim(country,'-%') from table

if anyone has any idea it would be great and would save some unnecessary java coding for me.

Many thanks.
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
Something like this should work :

SELECT SUBSTR( country, 1, INSTR(country, '-',1) - 1) as whatever
from table
Meghna Bhardwaj
Ranch Hand

Joined: Jun 08, 2007
Posts: 109
Hi Agador,

Thankyou very much, that works very well.
It extracts the string before the hyphen, however sometimes it returns null for String that do not have a hyphen. Is there any way to avaoid getting null in the result set. So that I only get non null values?

Many thanks once again.
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
use the NVL function :

SELECT NVL( SUBSTR( country, 1, INSTR(country, '-',1) - 1), country) as whatever
from table
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: String funciton in oracle database