substring update of field using interactive SQL

[Caroline]

Hello there,
Anybody able to help with following query......
I have a field EUSR1, 10 long. I want to update the first element and second
elements of these fields separately depending on the value of certain fields
in the file.
I want to use the UPDATE function in SQL but I don't know how to use the
SUSBTR to specify elements 1 and 2.
Please don't tell me to look at the IBM on line reader, I have already done
this and am none the wiser.!!!

Thanks in advance
Caroline Fallon
co...@compex.ie

[Simon Godden]

Hi Caroline,

This is perfectly possible, but a bit of a pain in the arse, because you
cannot use SUBSTR on the left hand side of an SQL operator. You can only
SET the entire contents of a field.

Taking your example of a 10 alpha field, here is how to change only the
first character to 'a', leaving the remainder of the field the same:

UPDATE file
SET field=('a' CONCAT SUBSTR(field, 2, 9))
WHERE someotherfield=whatever

the second example is even more annoying to construct. Here is how to set
only the second character to 'a', leaving all remaining characters
unchanged:

UPDATE file
SET field=(SUBSTR(field, 1, 1) CONCAT 'a' CONCAT SUBSTR(field, 3, 8))
WHERE someotherfield=whatever

Hope this helps you, let me know if you need further info explaining the
solutions, and if anyone else knows a better way, please tell me!

Cheers,

Simon.
Caroline wrote in message ...