Change a Substring with SQL

Published: April 5, 2006

Hey, Ted:

I am trying to change part of a character field with an SQL UPDATE command. However, my SQL command gives me an invalid token error at the opening parenthesis of SUBSTR in the SET statement. Is there a rule that you can't use SUBSTR in a SET statement? If so, do you have a workaround?

--Dennis

What you're trying to do is reasonable, Dennis. After all, RPG and CL let you change part of a character field using their %SUBST and %SST functions. But SQL is different.

For the benefit of other readers, here is the SQL command Dennis sent to me.

update library/file 
   set substr(rpc,1,3) = char(comno)
 where comno in (015,016) and substr(rpc,1,3) = ' '

Dennis wants to replace the first three characters of the RPC field with whatever is in COMNO, but only if those characters are blank and only for records in companies 015 and 016. The solution is to replace the entire field, not just part of it, by selecting the parts you wish to keep and replacing the part you wish to update, like this:

update library/file
   set rpc = char(comno) || substr(rpc,4)
 where comno in (015,016) and substr(rpc,1,3) = ' '

In this example, the new value of RPC is the three company number digits concatenated to whatever is in positions four and following.

Thanks for the question, Dennis. This is a handy technique to know.

--Ted