|
View previous topic :: View next topic
|
| Author |
Message |
ajeshrn
New User
Joined: 25 Mar 2009
Posts: 78
Location: India
|
|
|
|
Hi,
We have a scenario where we need to check the value of a column in db2 table and replace the first occurence of the space between words with a comma(,). Can someone suggest on how to achieve this. We are planning to use this query for a one time update on the table.
For example,
Current data in table
Test Name
Test
It should be replaced as below:
Test,Name
Test, |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| INSPECT nice-descriptive-data-name REPLACING FIRST SPACE BY COMMA. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Ahh...
You mean you want to have some SQL which behaves in the same way as the above? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
update tab1
set col1 =
case when (posstr(strip(col1),' ') > 0 ) then
substr(col1,1,posstr(remarks,' ') - 1) || ',' ||
substr(col1,posstr(remarks,' ') + 1)
else col1
end |
|
|
| Back to top |
|
|
ajeshrn
New User
Joined: 25 Mar 2009
Posts: 78
Location: India
|
|
|
|
Thanks a lot...It works and we were able to successfully update the table value with , for the first occurance of space...  |
|
| Back to top |
|
|
|
|
