Replacing the 113th position in a string with 'X' in DB2 SQL

Question

I am looking for a solution for replaceing the 113th position within a string.

As of now the only solution that I found was this one, however, it is not a solution because it replaces all spaces with X.

I cannot use the STUFF function or something like that because I am running on DB2 V11.2.

Here is what I have so far:

SELECT
  REPLACE(COLUMN_NAME, SUBSTR(COLUMN_NAME,113,1), 'N')
FROM TABLE_NAME;

The result is that all spaces are replaced with N. However, I just want the 113th position replaced with an 'N', and if the string is not that long, nothing should happen!

Since, as it seems, I have quite an old version of DB2, I have no CHARINDEX, STUFF, or whatsoever function available.

Maybe, someone has an idea how I can change just the character at position 113th?

Thank you very much in advance!

Answer 1

Please try this: overlay(column_name,'N', 113, 1)

Answer 2

For that you can use reg_replace

CREATE tABLE test (te CHAR(125))

INSERT INTO test VALUES('12345678901234567890234567890')

SELECT SUBSTR(TE,14,1) FROM test

1
4

SELECT
  REPLACE(TE, SUBSTR(TE,14,1), 'N')
FROM test;

1
123N567890123N56789023N567890

SELECT REGEXP_REPLACE(
  TE,SUBSTR(TE,14,1), 'N', 13,1,'c' )
      FROM test

1
1234567890123N567890234567890

fiddle