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!

Please specify your exact version of Db2-server and the platform (z/os , i series, linux / unix/windows/cloud ). — mao, Jun 19 '23 at 16:18
Its DB2 11.2. So, I was totally wrong with version 10. I am sorry and the environment is a Mainframe. — Bernd, Jun 19 '23 at 16:25
please use correct rdbms platform tags when asking for help, in this case db2-zos — mao, Jun 19 '23 at 16:27

score 1 · Accepted Answer · answered Jun 19 '23 at 16:24

1

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

answered Jun 19 '23 at 16:24

user2398621

86
1
3

Overlay seems to be working for me! Thank you very much for your help! – Bernd Jun 19 '23 at 16:39
Hm, I have an additional question: Something causes me an error when the string is longer than 113 characters. The string has trailing spaces. The trailing space at position 113 should be filled up with 'N'. As far I used the following command: OVERLAY (column_name,'N',113,1,,OCTETS) but it always abends with: The data type, length, or vlaue of argument 3 of SYSIBM.OVERLAY is invalid. – Bernd Jun 21 '23 at 13:19
Casting it to CHAR() seems to be working. – Bernd Jun 21 '23 at 13:52

score 0 · Answer 2 · answered Jun 19 '23 at 16:26

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

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

2 Answers2