Home » SQL & PL/SQL » SQL & PL/SQL » Trim Column Data (11g)
Trim Column Data [message #447062] Thu, 11 March 2010 15:06 Go to next message
ThereUare
Messages: 3
Registered: March 2010
Location: Melbourne, FL
Junior Member
Need to do this

Column1
--------
5648_6844_20020201
6878_6845_20051201
9845_6548_20080307

Need to change it to this

Column1
--------
20020201
20051201
20080307

So I basically need to remove the leading part of the string using the "_" underscore as the delimiter.

So I thought this would work, but no luck.
SELECT LTRIM('_', Column1) "NewCOL" From table;

Any help please!!!
Re: Trim Column Data [message #447063 is a reply to message #447062] Thu, 11 March 2010 15:08 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
use INSTR() & SUBSTR() functions

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Thu, 11 March 2010 15:09]

Report message to a moderator

Re: Trim Column Data [message #447064 is a reply to message #447063] Thu, 11 March 2010 15:22 Go to previous messageGo to next message
ThereUare
Messages: 3
Registered: March 2010
Location: Melbourne, FL
Junior Member
Thanks, but it only leaves the leading part of the string. I need to trailing part. So anything after the last underscore.

Can you give me an example. I'm fairly new at this.
Re: Trim Column Data [message #447065 is a reply to message #447064] Thu, 11 March 2010 15:28 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
When all else fails, Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions068.htm#i77598

"If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position."
Re: Trim Column Data [message #447098 is a reply to message #447064] Thu, 11 March 2010 22:14 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SELECT SUBSTR('5648_6844_20020201',INSTR('5648_6844_20020201','_',-1)+1) AS EXPR FROM DUAL;


regards,
Delna
Re: Trim Column Data [message #447116 is a reply to message #447062] Thu, 11 March 2010 23:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you are in 11g you can also use the "new" regexp functions:
SQL> select regexp_substr('5648_6844_20020201','[^_]*$') from dual;
REGEXP_S
--------
20020201

Regards
Michel
Re: Trim Column Data [message #447223 is a reply to message #447098] Fri, 12 March 2010 08:26 Go to previous messageGo to next message
ThereUare
Messages: 3
Registered: March 2010
Location: Melbourne, FL
Junior Member
Thank you all for your help.

This seemed to work great.

SELECT SUBSTR(Column1,INSTR(Column1,'_',-1)+1) AS Col1 FROM table;
Re: Trim Column Data [message #447288 is a reply to message #447062] Sat, 13 March 2010 00:42 Go to previous messageGo to next message
tejas.patel
Messages: 22
Registered: December 2008
Location: NJ
Junior Member

goto SUBSTR function.
Re: Trim Column Data [message #447295 is a reply to message #447288] Sat, 13 March 2010 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
tejas.patel wrote on Sat, 13 March 2010 07:42
goto SUBSTR function.

Please read the whole topic before posting and
1/ avoid to post if you have nothing to add
2/ learn from the previous posts

Regards
Michel

Re: Trim Column Data [message #447346 is a reply to message #447288] Sat, 13 March 2010 15:46 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
tejas.patel wrote on Sat, 13 March 2010 07:42
goto SUBSTR function.

SUBSTR function. is an invalid label for the GOTO command ./fa/1986/0/
Previous Topic: Problem with (seemingly) simple sql query
Next Topic: Java Stored Procedure
Goto Forum:
  


Current Time: Sat Dec 03 18:04:45 CST 2016

Total time taken to generate the page: 0.04257 seconds