CODEWITHSUNDEEP
sqldb2
em3ricasforsale
em3ricasforsale

Reputation: 352

update data column with data from another column plus an additional keyword

I have tried using a combination of both of these two previous questions Question 1 Question 2 to help me with my issue.

My issue is I am trying to set one column in DB2 equal to another column with a beginning and ending value. I do have the option of doing this in two steps, of adding the S- first and then on a second pass add the -000 on the end, but I am currently running into the issue of CONCAT not working in DB2 like in MYSQL.

Pre-conversion

name  |   loc      |  group
-----------------------------------
sam   |   123      |   
jack  |   456      |   
jill  |   987      |   
mark  |   456      |   
allen |   123      |   
john  |   789      |   
tom   |   123      |

Post-conversion

name  |   loc      |  group
-----------------------------------
sam   |   123      |   S-123-000
jack  |   456      |   S-456-000
jill  |   987      |   S-987-000
mark  |   456      |   S-456-000
allen |   123      |   S-123-000
john  |   789      |   S-789-000
tom   |   123      |   S-123-000

The SQL I am trying to use:

UPDATE table 
SET GROUP = CONCAT('S-',LOC,'-000') 
WHERE LENGTH(RTRIM(LOC)) = 3

Any help or guidance would be appreciated.

Upvotes: 1

Views: 11097

Answers (2)

keV
keV

Reputation: 349

There is also a concatenate operator: ||

However some recommend not to use it

UPDATE table 
SET GROUP = 'S-'||LOC||'-000'
WHERE LENGTH(RTRIM(LOC)) = 3

If you're LOC field is longer than 3 and you want no whitespace

UPDATE table 
SET GROUP = 'S-'||RTRIM(LOC)||'-000'
WHERE LENGTH(RTRIM(LOC)) = 3

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

The DB2 CONCAT takes only two arguments, so you could nest them to achieve your desired result.

UPDATE table 
SET GROUP = CONCAT(CONCAT('S-',LOC),'-000') 
WHERE LENGTH(RTRIM(LOC)) = 3

Upvotes: 1

Related Questions