SQL INSERT scalar function in DB2 for i SQL

SQL INSERT scalar function in DB2 for i SQL

INSERT

The INSERT function returns a string where beginning at start in source-string, length characters have been deleted from source-string and insert-string has been inserted.

Syntax

INSERT(source-string, start, length, insert-string)

Example #1:

SELECT    INSERT('VEGETABLE',1,2,'00'),     
               INSERT('VEGETABLE',1,0,'00'), 
               INSERT('VEGETABLE',1,2,'0'),    
               INSERT('VEGETABLE',1,2,' '),      
               INSERT('VEGETABLE',3,2,'00'),   
               INSERT('VEGETABLE',3,0,'00'),    
               INSERT('VEGETABLE',10,0,'00')     
     FROM SYSIBM.SYSDUMMY1                       

Output

INSERT     INSERT       INSERT    INSERT    INSERT     INSERT       INSERT     
00GETABLE  00VEGETABLE  0GETABLE   GETABLE  VE00TABLE  VE00GETABLE  VEGETABLE00

INSERT('VEGETABLE',1,2,'00'): This would delete 1 and 2 positions in source-string and insert 00 in place of that and make it 00GETABLE.

INSERT('VEGETABLE',1,0,'00'): This would insert 00 before 1st position in source-string and make it 00VEGETABLE.

INSERT('VEGETABLE',1,2,'0'): This would delete 1 and 2 positions in source-string and insert 0 in place of that and make it 0GETABLE.

INSERT('VEGETABLE',1,2,' '): This would delete 1 and 2 positions in source-string and insert 1 blank in place of that and make it ' GETABLE'.

INSERT('VEGETABLE',3,2,'00'): This would delete 3 and 4 positions in source-string and insert 00 in place of that and make it VE00TABLE.

INSERT('VEGETABLE',3,0,'00'): This would insert 00 before 3rd position in source-string and make it VE00GETABLE.

INSERT('VEGETABLE',10,0,'00'): This would insert 00 after 10th position in source-string and make it VEGETABLE00.