aspose 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


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
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
 
Similar Threads
Using Hibernate with Spring
Can't access Oracle Schema for query
field name problems with joins in Oracle JDBC thin driver
SQLException on column name
iBATIS - Error while accessing a column with hyphen in the name