OraFAQ Forum: SQL & PL/SQL » How to update substring using sql

Home » SQL & PL/SQL » SQL & PL/SQL » How to update substring using sql (oracle 11g)

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

How to update substring using sql [message #639491]

Wed, 08 July 2015 10:44

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Hello all,

I need to update the substring of a table column, how can i do that?. my table column reuturns value as

YY99YYYYYYY

, if i want to update particular substring location how can i do that.

Ex: YY99YYYYYYY update the highlighted from Y to N.

appreciate your inputs.

[Updated on: Wed, 08 July 2015 10:46]

Report message to a moderator

Re: How to update substring using sql [message #639492 is a reply to message #639491]

Wed, 08 July 2015 10:54

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Please explain the rule. It is not clear what you want. Please post a test case.

Is the highlighted 'Y' always going to be the 4th character from reverse of the string?

[Updated on: Wed, 08 July 2015 10:55]

Report message to a moderator

Re: How to update substring using sql [message #639493 is a reply to message #639492]

Wed, 08 July 2015 11:42

gorants
Messages: 85
Registered: May 2014
Location: ATL

Member

Sorry for not being clear. scenario is like if we want to update any character in given string of specified location ( Ex: 1 , 5 , 6 character position etc)

Report message to a moderator

Re: How to update substring using sql [message #639494 is a reply to message #639493]

Wed, 08 July 2015 11:51

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

Senior Member
Account Moderator

Regular expressions might help, such as in this example; I'm substituting one character at the V_POSITION position with a '#'

SQL> select regexp_replace('YY99YYYYYYY', '.{1}', '#', &v_position, 1) result
  2  from dual;
Enter value for v_position: 2

RESULT
-----------
Y#99YYYYYYY

SQL> /
Enter value for v_position: 8

RESULT
-----------
YY99YYY#YYY

SQL>

Report message to a moderator

Re: How to update substring using sql [message #639495 is a reply to message #639493]

Wed, 08 July 2015 11:59

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

gorants wrote on Wed, 08 July 2015 22:12

scenario is like if we want to update any character in given string of specified location ( Ex: 1 , 5 , 6 character position etc)

So, you know the INSTR i.e. position of the character in the string to be updated? Isn't it? Then you just need to use UPDATE statement with INSTR in the predicate.

Report message to a moderator

Re: How to update substring using sql [message #639496 is a reply to message #639491]

Wed, 08 July 2015 12:02

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

Senior Member
Account Moderator

Tell me if I am wrong but you never feedback, you never thank people who helped you and you always refuse to post a test when we have repeatedly asked you.

Now, tell us why we should continue to help you?

Report message to a moderator

Re: How to update substring using sql [message #639497 is a reply to message #639496]

Wed, 08 July 2015 12:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Michel Cadot wrote on Wed, 08 July 2015 10:02

Tell me if I am wrong but you never feedback, you never thank people who helped you and you always refuse to post a test when we have repeatedly asked you.

Now, tell us why we should continue to help you?

Report message to a moderator

Previous Topic:	Extracting the Numeric values from a column in oracle
Next Topic:	How to use computed result of one row in the next row for inserting in table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 10 05:59:20 CDT 2026