Updating data in VARCHAR field

sunny_io · New User Joined: 11 Jul 2007 Posts: 68 Location: noida

Hello Guru's

UPDATE table-name
SET SUBSTR(VARFIELD,10,10) = 'ABCDEFGHIJ'
WHERE SUBSTR(VARFIELD,10,10) = 'XXXXXXXXXX'

Is giving me syntax error on the SQl -104 .. ILLEGAL SYMBOL '('

My requirement is to identify and update all rows where from byte 10 to 10 in the VARCHAR field, the data is a certain predefined value.

Wondering if I can update part f the data in a VARCHAR field in SPUFI as opposed to writing an Application program for it. Any other suggestions are welcome.

The length of data would not change

Many Thanks
Sunny

mjadwani2785 · Posted: Sat Dec 20, 2014 6:51 am

Hi ,

If I have understood your query (english) correctly may be you are looking for something like this

UPDATE table-name
SET VARFIELD = CONCAT( SUBSTR(VARFIELD,1,9),'ABCDEFGHIJ')
WHERE SUBSTR(VARFIELD,10,10) = 'XXXXXXXXXX' ;

PS: Try it at your own risk :-)

Regards,
Manoj Jadwani

Rohit Umarjikar · Posted: Tue Dec 23, 2014 2:21 am

Manoj, Yes, that should work.