Concat column value and variable with specific length in MySQL

Question

I have a table products and another table categories.
Here is some sample data
Products

id    |  product_name      |  category_id
1     |  Leath Machine     |  1
2     |  Drilling Machine  |  1
3     |  Boring Machine    |  1

And here is category table

id    |   name           | symbol
1     |   SMT Equipment  | SMT

I want to do a select which will display me results like this

id    |  product_name      |  category_id  |  code
1     |  Leath Machine     |  1            |  SMT00001
2     |  Drilling Machine  |  1            |  SMT00002
3     |  Boring Machine    |  1            |  SMT00003

How cai do that.

Muhammad Raheel · Accepted Answer · 2013-02-22 06:13:29Z

4

SELECT
  `products`.`id`,
  `product_name`,
  `category_id`,
  CONCAT(symbol, LPAD(products.id,5,'0')) AS code
FROM products
  INNER JOIN categories
    ON products.category_id = categories.id;

Here is the SQLFiddle results.

SQL Fiddle Demo

edited Feb 22, 2013 at 6:13

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

answered Feb 21, 2013 at 20:11

Slowcoder

2,1303 gold badges17 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

echo_Me · Accepted Answer · 2013-02-21 20:07:28Z

2

try this

   select p.id,p.product_name,p.category_id,concat(symbol ,'0000',p.id) code from Products p
   inner join category c
   on p.category_id = c.id

DEMO SQLFIDDLE HERE

answered Feb 21, 2013 at 20:07

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

1 Comment

Muhammad Raheel Over a year ago

this will not bring the desired result if there are 10 or more products. it will still fetch 0000 as string which should have been incremented.

arun · Accepted Answer · 2013-02-21 20:09:53Z

1

SELECT p.*, CONCAT(c.symbol, LPAD(p.id, 5, '0')) FROM products p INNER JOIN category c ON p.category_id=c.id;

answered Feb 21, 2013 at 20:09

arun

11.1k7 gold badges67 silver badges84 bronze badges

Collectives™ on Stack Overflow

Concat column value and variable with specific length in MySQL

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related