MVSFORUMS.com

rani · Beginner Joined: 21 Sep 2004 Posts: 2 Topics: 1

hi board,

Is there a way to insert a value in middle of a column...if there is a way...how is it possible...

Cheers
Rani

kolusu · Posted: Tue Sep 21, 2004 5:53 am Post subject:

rani,

You need to tell us a little bit more. what is the column definiton? is it a char or decimal? Please provide us with a sample input

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Siva Kumar Sunku · Beginner Joined: 17 Aug 2004 Posts: 25 Topics: 14

Hi Rani,
If the column is char type, following may help you.
Split the column by using Substring function, Reformat/Add (as you need)and again combine them using Concat function.

Ex - If you need to insert 'XYZ' in the column from 3rd position onwards.

astro · Beginner Joined: 05 Oct 2005 Posts: 31 Topics: 7

Dear Siva,
Your logic is good. But the scalar function CONCAT can not have more than 2 arguements as you have mentioned in your reply.
If the arguments are more than 2, as in this case, you need to mention as:

shekar123 · Posted: Sun Mar 05, 2006 1:27 am Post subject:

Hi All,

If we had a table named NAME and one of the column had value 'SHEKAR' and if need to introduce '1234' in the middle of the column to get 'SHE1234KAR' ,here is what we get:

Code:

CREATE TABLE SHEKAR.NAME
(
NAME CHAR(10) NOT NULL,
)
IN DBTEMP.TBTEMP;
INSERT INTO SHEKAR.NAME VALUES ('SHEKAR');

IF WE CODE AS PER SHIVA

Code:

UPDATE SHEKAR.NAME
SET NAME = CONCAT( SUBSTR (NAME,1,3),
'1234',
SUBSTR (NAME,4,3)) WHERE NAME = 'SHEKAR';
WE GET SQL ERROR:
DSNT408I SQLCODE = -170, ERROR: THE NUMBER OF ARGUMENTS SPECIFIED FOR CONCAT
IS INVALID

IF WE CODE AS PER ASTRO

Code:

UPDATE SHEKAR.NAME
SET NAME = SUBSTR(NAME,1,3) CONCAT '1234' CONCAT SUBSTR(NAME,4,3)
WHERE NAME = 'SHEKAR';

OUTPUT

Code:

SHE1234

Shiva and Astro can u please guide me as why i am not getting my desired result as 'SHE1234KAR' ?
_________________
Shekar
Grow Technically

kolusu · Posted: Mon Mar 06, 2006 9:56 am Post subject:

shekhar123,

try this

shekar123 · Posted: Mon Mar 06, 2006 11:19 pm Post subject:

Thanks Kolusu,

I was able to get the desired result.
_________________
Shekar
Grow Technically