CODEWITHSUNDEEP
sqlinsertnew-operatorif-statement
jpo
jpo

Reputation: 4059

MySql Use existing column to create and populate new column

I have a table with values. I am trying to create a new column whose row value will depend on that of an existing column. For example, in the table below, if the substr of the id column from index 6 to index 7 is greater than 10, then the value of the new column at that row should be the sub string of the id at index 1-4 hyphen substr(id, 1, 4)+1. Else the value should be substr(id, 1, 4) -1 hyphen substr(id, 1, 4). 

id          New Column  Name
M20061012   2006-2007   Jude
K20070212   2006-2007   Anne
D20071214   2007-2008   John

Here is the algorithm for each row of the table:

if substr(id, 5, 1) < 10
   New Column value at that row = substr(id, 1, 4) - substr(id, 1, 4)+1
else
   New Column value at that row = substr(id, 1, 4)-1 - substr(id, 1, 4)

Any help will be greatly appreciated Thanks

EDIT: So I tried this as suggested by @scwagner

SELECT
id,
(CASE WHEN CONVERT(SUBSTR(id,6,2), SIGNED) < 10 
THEN CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED)-1,'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)) 
ELSE CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED),'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)+1)   END) AS `New Column`,
Name
FROM new

Where new is the name of the table. But this returns a column with all row values = BLOB. How can I fix that?

Upvotes: 0

Views: 732

Answers (2)

jpo
jpo

Reputation: 4059

Figured it out: 

 SELECT
    id,
    (CASE WHEN CONVERT(SUBSTR(id,6,2), SIGNED) < 10 
    THEN CONCAT(CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED)-1 AS CHAR),'-',CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED) AS CHAR)) 
    ELSE CONCAT(CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED) AS CHAR),'-',CAST(CONVERT(SUBSTR(id, 2, 4),SIGNED)+1) AS CHAR)   END) AS `New Column`,
    Name
    FROM new

Upvotes: 0

scwagner
scwagner

Reputation: 4005

You may want to test this on more data, but this worked on MySQL 5.1.41-3ubuntu12.8 with the test data that you provided:

SELECT
  id,
  (CASE WHEN CONVERT(SUBSTR(id,6,2), SIGNED) < 10 THEN CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED)-1,'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)) ELSE CONCAT(CONVERT(SUBSTR(id, 2, 4),SIGNED),'-',CONVERT(SUBSTR(id, 2, 4),SIGNED)+1) END) AS `New Column`,
  Name
FROM
  <your table>

Upvotes: 1

Related Questions