• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

String funciton in oracle database  RSS feed

 
Ranch Hand
Posts: 109
  • 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.
 
Ranch Hand
Posts: 118
  • 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
  • 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
  • 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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!