• 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:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Liutauras Vilda
Sheriffs:
  • Tim Cooke
  • Devaka Cooray
  • Paul Clapham
Saloon Keepers:
  • Tim Moores
  • Frits Walraven
  • Ron McLeod
  • Ganesh Patekar
  • salvin francis
Bartenders:
  • Tim Holloway
  • Carey Brown
  • Stephan van Hulst

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
 
Normally trees don't drive trucks. Does this tiny ad have a license?
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!