Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Update a certain part of column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Tue May 23, 2006 10:31 am    Post subject: Update a certain part of column
Reply with quote

Hi,

i want to update a certain part of column .

eg :

there is column NAME (char(20)) .
i want to change first letter of column NAME from 'a' to 'b' .

so i want to know can i use substring command with update .

Thanks in advance,
-Kapil .
Back to top
View user's profile Send private message

Gurmeet

New User


Joined: 22 Feb 2006
Posts: 46
Location: Pune

PostPosted: Tue May 23, 2006 10:16 pm    Post subject: Re: update part of column
Reply with quote

Kapil,

User this query,

UPDATE Table_name SET name = 'b' || SUBSTR(name,2)
WHERE name = 'abc';

~Gurmeet
Back to top
View user's profile Send private message
krish_mrt

New User


Joined: 15 Apr 2005
Posts: 12
Location: Chennai

PostPosted: Wed May 24, 2006 2:43 pm    Post subject: Re: update part of column
Reply with quote

Hi,
Try this update

Code:
update table_name
set name = 'b'||substr(name,2)
where name like 'a%'
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Wed May 24, 2006 2:51 pm    Post subject: Re: update part of column
Reply with quote

Thanks Buddy .
this seems to be perfect

-Kapil .
Back to top
View user's profile Send private message
rexzone1

New User


Joined: 16 Dec 2006
Posts: 14
Location: Halifax, Canada

PostPosted: Wed Nov 19, 2008 7:46 am    Post subject: Same operation on a numeric field
Reply with quote

Hi,

I need to do the same operation(Update particular part of a column) on a numeric field.
Could you please suggest me a way if there is any (without using a program).

Thanks.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Nov 19, 2008 8:30 am    Post subject:
Reply with quote

Hello,

Possibly, if what you need to do can be accomplished by some calculaton. . .

Otherwise, write the code. . .
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Wed Nov 19, 2008 7:37 pm    Post subject: Reply to: update part of column
Reply with quote

Well, it would be better if you can provide exact requirement details.

-Thanks,
-Kapil.
Back to top
View user's profile Send private message
Vivek_Vasudevamurthy

New User


Joined: 19 Sep 2008
Posts: 9
Location: Bangalore,Karnataka

PostPosted: Thu Nov 20, 2008 12:08 pm    Post subject:
Reply with quote

Hi friends,

if i want change any letter in center or set of letters how it will work

For a example

"ABCDEF"

now i want to change "CDE" to "XYZ"

==============================
User this query,

UPDATE Table_name SET name = 'b' || SUBSTR(name,2)
WHERE name = 'abc';

in this query what 2 stands for
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 70
Location: bangalore

PostPosted: Thu Nov 20, 2008 1:22 pm    Post subject: Reply to: Update a certain part of column
Reply with quote

Let me put in some idea on how the substr command works.

when i say SET name = 'b' || SUBSTR(name,2)

then the the letter 'b' in the first position (i+1) which is 1+1 in this case will be considered.

If you want to change the first two characters it should be

'bc' || SUBSTR(name,3) ----- i+2

One thing i ma not clear is what can be done when we require a substring to slect from some where in the middle.

I assue there should be one more parameter in the SUBSTR command in that case.

Can some body suggest on this?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 20, 2008 10:50 pm    Post subject:
Reply with quote

Hello,

Something to keep in mind is that many/most data manipulation requirements will not be met by some simple sql query. . .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us