SQL update a value within a string from another column

Question

I have a column which contains a string within this string their is a GUID see below called voucherID, I would like to replace this GUID from another column on the same row.

<VoucherID>6c1c5c4f-3bab-4804-9a92-80b34f448cfe<VoucherID>

any help appreciated.

There are around 6000 rows.

Is the GUID to be replaced different on each row or the same? And what RDBMS are you using? — Martin Smith
– Martin Smith, Commented Nov 6, 2011 at 10:50
What datatype (and name) is your column that holds the XML-enclosed GUID ?? — marc_s
– marc_s, Commented Nov 6, 2011 at 16:56

Yahia · Accepted Answer · 2011-11-06 19:45:38Z

1

IF on Oracle try

UPDATE YourTable T SET
T.YourXMLColumn = SUBSTR ( T.YourXMLColumn, 1, INSTR ( T.YourXMLColumn, '>' ) + 1 ) || 
                  T.YourValueColumn || 
                  SUBSTR ( T.YourXMLColumn, INSTR (T.YourXMLColumn, '<', -1 ) ) ;

Another option is just rebuilding the content with the new value

UPDATE YourTable T SET
T.YourXMLColumn = '<VoucherID>' || 
                  T.YourValueColumn || 
                  '</VoucherID>';

edited Nov 6, 2011 at 19:45

answered Nov 6, 2011 at 17:04

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gp2mv3 · Accepted Answer · 2011-11-06 10:52:57Z

0

Why don't you select the value in the right colomn and then make an update ? If you've a lot of switches to do you can use a loop, offcours it can be realy slow but it's the easier way, I think...

answered Nov 6, 2011 at 10:52

Gp2mv3

1,45121 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL update a value within a string from another column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related