Re: Replacing characters inside of a string

On Mon, 2022-03-07 at 17:21 +0000, Jack Prucha wrote:

I have a requirement to change some character fields in a file. 
Leave the first and last character as is and replace all the between
characters with a fill character such as an asterisk.

For example:

'Jack' = 'J**K'
'iSeries = 'i*****s'

I'm testing SQL statements with hard-coded strings.  I thought I had
a solution with TRANSLATE because it has a fill character parameter
but it wants to translate the 1st and last characters if they happen
to match one of the between characters.  I don't know where to begin
with perhaps REGEXP_REPLACE.    I'm back at square one now and don't
see any straightforward SQL methods to do this.  I know this can be
done with RPG but hoping to do this without 'code'.

TIA
Jack

Having checked the IBM/i SQL manual it has the "replace" function so
all you'd need to do is use the following: 

UPDATE this_table SET this_column = replace(this_column, 'Jack',
'J**k')

And then repeat for as many replacements as required.

I've not tried it, but I wonder if you could nest the replaces together
eg. SET this = replace((this = replace, 'bbbb', 'b**b'), 'aaaa',
'a***a')

Or if you can some how chain the replace's together.

Which if possible is probably quicker than running multiple UPDATE's
for each change.

(I only know of this as I was needing to change some website url's in
my local wordpress instance as I changed the computers name and this
came up as the way to do it at the database level.)

Jon