Hi, I have a requirement where in I need to update data at multiple positions of a varchar field. The field MEMGRP is of length 55 (basically PIC X(11) OCCURS 5 TIMES).
I need to replace SPACES with ZERO's.
If SUBSTR(MEMGRP,5,7) = ' ' then
set SUBSTR(MEMGRP,5,7) = '0000000'
If SUBSTR(MEMGRP,16,7) = ' ' then
set SUBSTR(MEMGRP,16,7) = '0000000'
If SUBSTR(MEMGRP,27,7) = ' ' then
set SUBSTR(MEMGRP,27,7) = '0000000'
If SUBSTR(MEMGRP,38,7) = ' ' then
set SUBSTR(MEMGRP,38,7) = '0000000'
If SUBSTR(MEMGRP,49,7) = ' ' then
set SUBSTR(MEMGRP,49,7) = '0000000'
I executed the following query and able to update for the first occurence successfully.
UPDATE MEMMSTR
SET MEMGRP = (SUBSTR(MEMGRP,1,4) || '0000000' || SUBSTR(MEMGRP,12,44)) where SUBSTR(MEMGRP,5,7) = ' '
But I need to update all the occurences in one shot.
I understand that this can be easily done using a cobol program. But I am actually looking to do it using a update query.
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
unless the varchar COLUMN in all rows is max length,
you will need to include some length checks to prevent negative sqlcode returns.
translate or replace would be good options for this.
IMUO:
small varchar fields are really useless with DB2's datacompression.
varchar sql syntax requires (especially in this case) knowing the length of the column.
sounds as if you are trying to initialize these varchar columns,
if each varchar column is 55, a simple set of CASE statements could perform this in one shot.
but, if there are differing lengths, you end up with a set of convoluted CASE statements, and since I don't know how to code a NOP (no operation) for the ELSE clause in a CASE statement,
can't help you.
your reluctance to run multiple queries is silly,
by now you could have done it.
update MEMMSTR
set MEMGRP =
substr(MEMGRP,1,4)||
case when substr(MEMGRP,5,7) = ' ' then '0000000' else substr(MEMGRP,5,7) end ||
substr(MEMGRP,12,4)||
case when substr(MEMGRP,16,7) = ' ' then '0000000' else substr(MEMGRP,16,7) end ||
substr(MEMGRP,23,4)||
case when substr(MEMGRP,27,7) = ' ' then '0000000' else substr(MEMGRP,27,7) end ||
substr(MEMGRP,34,4)||
case when substr(MEMGRP,38,7) = ' ' then '0000000' else substr(MEMGRP,38,7) end ||
substr(MEMGRP,45,4)||
case when substr(MEMGRP,49,7) = ' ' then '0000000' else substr(MEMGRP,49,7) end
where
substr(MEMGRP,5,7) = ' '
or substr(MEMGRP,16,7) = ' '
or substr(MEMGRP,27,7) = ' '
or substr(MEMGRP,381,7) = ' '
or substr(MEMGRP,49,7) = ' '