Home » SQL & PL/SQL » SQL & PL/SQL » substr and instr
substr and instr [message #191450] Wed, 06 September 2006 07:13 Go to next message
bella13
Messages: 90
Registered: July 2005
Member

I am writing an SQL to strip off characters upto 0. We are migrating the data from a different database where values are sotred as 007809, I need to show 7809.

Here is some data I am working on,the size of the column is varchar2(12)

U0000011287
U0000011290
U0000003742
U0000014950

So basically for all of those I need to show


11287
11290
3742
14950

Sometimes the 0 comes in the 6th or the 8th positions. Need to stripp of from the left upto the last zero.

I tried to use substr and instr. But for U0000003742 it gives me "03742" when it shd give me "3742" while for U0000011290 it gives me "0" while it shd be returning 11290

Any help/ hints will be appreciated. thanks for your time.

bella,

Re: substr and instr [message #191455 is a reply to message #191450] Wed, 06 September 2006 07:19 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
This may help;

SELECT LTRIM( SUBSTR( t.char_value, INSTR( t.char_value , '00',-1)  ), '0')  FROM char_value_table t

[Updated on: Wed, 06 September 2006 07:20]

Report message to a moderator

Re: substr and instr [message #191456 is a reply to message #191455] Wed, 06 September 2006 07:23 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
But this is even better
SELECT LTRIM( SUBSTR( t.char_value, INSTR( t.char_value , '0')  ), '0')  FROM char_value_table t
Re: substr and instr [message #191460 is a reply to message #191456] Wed, 06 September 2006 07:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ltrim(ltrim(column, 'U'), '0')

[Updated on: Wed, 06 September 2006 07:32]

Report message to a moderator

Re: substr and instr [message #191461 is a reply to message #191456] Wed, 06 September 2006 07:41 Go to previous messageGo to next message
bella13
Messages: 90
Registered: July 2005
Member
aorehek

worked well..thanks
Re: substr and instr [message #191465 is a reply to message #191461] Wed, 06 September 2006 07:57 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Just to throw in another one. With the limited number of varying data you showed, this works:
to_number(substr(column,2))
Previous Topic: Call a function with parameters passed as a XML
Next Topic: Use of bulk bind
Goto Forum:
  


Current Time: Sun Dec 04 14:34:46 CST 2016

Total time taken to generate the page: 0.10669 seconds