OraFAQ Forum: SQL & PL/SQL » Update table removing first 2 digits from a column

Home » SQL & PL/SQL » SQL & PL/SQL » Update table removing first 2 digits from a column (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update table removing first 2 digits from a column [message #581722]

Wed, 10 April 2013 03:00

apenkov
Messages: 20
Registered: October 2012

Junior Member

Hello,
I have a table like:

0035987850    P
0035987851    P
0035987852    P

I would like to update removing 00 in the first column. So after update to have:

35987850    P
35987851    P
35987852    P

Thanks!

Report message to a moderator

Re: Update table removing first 2 digits from a column [message #581724 is a reply to message #581722]

Wed, 10 April 2013 03:23

Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Use SUBSTR function. Or, alternatively, if all strings begin with '00', use LTRIM.

[Updated on: Wed, 10 April 2013 03:25]

Report message to a moderator

Re: Update table removing first 2 digits from a column [message #581725 is a reply to message #581722]

Wed, 10 April 2013 03:24

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Always remove first two digits, or remove leading zeros?

Report message to a moderator

Re: Update table removing first 2 digits from a column [message #581728 is a reply to message #581722]

Wed, 10 April 2013 03:28

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And if you don't how many 0 and field value is always a number:

set field=TO_CHAR(TO_NUMBER(field))

This will validate that all rows have a number in this field.
But, obviously, the correct change would be to change the datatype (unless this field does not denote a number).

Regards
Michel

Report message to a moderator

Re: Update table removing first 2 digits from a column [message #581734 is a reply to message #581725]

Wed, 10 April 2013 03:33

apenkov
Messages: 20
Registered: October 2012

Junior Member

I need to remove only leading two 00.

[Updated on: Wed, 10 April 2013 03:35]

Report message to a moderator

Re: Update table removing first 2 digits from a column [message #581735 is a reply to message #581734]

Wed, 10 April 2013 03:49

apenkov
Messages: 20
Registered: October 2012

Junior Member

Guys, at the end it turns out that I don't need any zeroes in front, so that works perfect:

update tmp_test
set b_no = ltrim(b_no, '0')
where cdr_snd = 'P'

Thanks All!!

Report message to a moderator

Previous Topic:	oracle developer
Next Topic:	SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 20 11:41:42 CDT 2025