This week I will draw your attention towards the SQL function INSERT. DB2 contains nowadays countless numbers of functions and it is virtually impossible to know them all. The INSERT function is one of the functions I have used with great pleasure. So what can INSERT do for you? I have used it to ease the construction of character strings.

Imagine a task where you need to replace some characters in a CHAR or VARCHAR column with another value. Before I discovered INSERT I did like this:

UPDATE MYTABLE
SET CHARCOL = SUBSTR(CHARCOL, 1, 10) !! 'NEW' !! SUBSTR(CHARCOL, 14)
WHERE SUBSTR(CHARCOL, 11, 3) = 'OLD'

This is a bit clumsy especially when I can use INSERT instead:

UPDATE MYTABLE
SET CHARCOL = INSERT(CHARCOL, 11, 3, 'NEW')
WHERE SUBSTR(CHARCOL, 11, 3) = 'OLD'

Please notice the consistency between the starting position and the length in the SET clause and the WHERE clause. In the "old" solution the risk of specifying a wrong length or position is a lot higher. By the way INSERT may be used to insert more characters and to remove characters. Please try this SQL statement to see how it works:

SELECT INSERT('Kilroy is here', 8, 2, 'was')
     , INSERT('Never write never', 7, 5, 'say')
FROM SYSIBM.SYSDUMMY1