CODEWITHSUNDEEP
mysqlsql
caramba
caramba

Reputation: 22490

SQL string concatenation for select output value with if

Having a table named blubb with 3 fields: id, year and week

CREATE TABLE blubb (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `year` int(11) NOT NULL,
  `week` int(11) NOT NULL
);


INSERT INTO blubb (year, week) VALUES (2017, 1);
INSERT INTO blubb (year, week) VALUES (2017, 2);
INSERT INTO blubb (year, week) VALUES (2017, 3);
INSERT INTO blubb (year, week) VALUES (2017, 4);
INSERT INTO blubb (year, week) VALUES (2017, 5);
INSERT INTO blubb (year, week) VALUES (2017, 6);
INSERT INTO blubb (year, week) VALUES (2017, 7);
INSERT INTO blubb (year, week) VALUES (2017, 8);
INSERT INTO blubb (year, week) VALUES (2017, 9);
INSERT INTO blubb (year, week) VALUES (2017,10);
INSERT INTO blubb (year, week) VALUES (2017,11);
INSERT INTO blubb (year, week) VALUES (2017,12);
INSERT INTO blubb (year, week) VALUES (2017,13);
INSERT INTO blubb (year, week) VALUES (2017,14);
INSERT INTO blubb (year, week) VALUES (2017,15);

The goal is to get a select statement which concatenats year and week with a W in between and which will always select the week with two digits. So if week value is 1 i need 01

I've accomplished it like so:

SELECT DISTINCT 
    IF(week < 10, CONCAT(year, 'W0', week), CONCAT(year, 'W', week) ) as year_week,
    year,
    week
FROM
    blubb

Result and also expected result:

year_week   year    week
2017W01     2017    1
2017W02     2017    2
2017W03     2017    3
2017W04     2017    4
2017W05     2017    5
2017W06     2017    6
2017W07     2017    7
2017W08     2017    8
2017W09     2017    9
2017W10     2017    10
2017W11     2017    11
2017W12     2017    12
2017W13     2017    13
2017W14     2017    14
2017W15     2017    15

The question is: Is there another better, cleaner way of solving this? Thank you

SQL Fiddle here

Upvotes: 0

Views: 30

Answers (2)

Indent
Indent

Reputation: 4967

Using LPAD :

SELECT
    CONCAT(year, 'W', LPAD(week, 2, '0'))as year_week,
    year,
    week
FROM
    blubb

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10711

You may use LPAD

select concat(year, 'W', lpad(week, 2, '0')) as year_week,
       year,
       week
from blubb

demo

Upvotes: 1

Related Questions