File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

String funciton in oracle database

 
Meghna Bhardwaj
Ranch Hand
Posts: 109
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 118
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Something like this should work :

SELECT SUBSTR( country, 1, INSTR(country, '-',1) - 1) as whatever
from table
 
Meghna Bhardwaj
Ranch Hand
Posts: 109
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 118
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic