How to extract numbers?

Question

I have a database like that:

stockCode stockName stockStart stockFinish stockCurrentNumber
__________________________________________________________
100       Water         1          10          ?

Now how do I code it so that it looks like this...

stockCode stockName stockStart stockFinish stockCurrentNumber
__________________________________________________________
100001       Water         1          10          001

100002       Water         1          10          002

100003       Water         1          10          003

100004       Water         1          10          004

100005       Water         1          10          005

100006       Water         1          10          006

100007       Water         1          10          007

100008       Water         1          10          008

100009       Water         1          10          009

100010       Water         1          10          010

I would greatly appreciate it if someone could help

SORRY, EDIT, I ONLY HAVE ONE RECORD, AND WANT IT TO BE DUPLICATED IN THE RANGE stockStart to stockFinish

@BhupeshC they are both `SMALLINT`. How do I extract all the numbers from stockStart to stockFinish and insert them into stockCurrentNumber?? — user3138212, Mar 20 '14 at 21:14
if the last column is SMALLINT, how does it show preceding 0s? — KrazzyNefarious, Mar 20 '14 at 21:18

M Khalid Junaid · Answer 1 · 2014-03-21T07:07:22.547

Try this

SELECT   
CONCAT(`stockCode`,lpad(@sno + 1,3,0)) stockCode,
`stockName`, 
`stockStart`,
`stockFinish`,
@sno := lpad(@sno + 1,3,0) AS stockCurrentNumbe
FROM  Table1,
(SELECT @sno := 000) t

Fiddle

Edit

For update you can create new table (for reference see fiddle demo) using the below query and once done you can rename your table to the actual table and for below example i have use int() not smallint

CREATE TABLE Table2
    (`stockCode` int(11),
     `stockName` varchar(5),
     `stockStart` int,
     `stockFinish` int, 
     `stockCurrentNumber` int(11))
;
INSERT INTO Table2
    (`stockCode`, 
     `stockName`, 
     `stockStart`,
     `stockFinish`, 
     `stockCurrentNumber`)

SELECT   
CONCAT(`stockCode`,lpad(@sno + 1,3,'0')) stockCode, 
  `stockName`, 
  `stockStart`,
  `stockFinish`,
  @sno := lpad(@sno + 1,3,'0') AS stockCurrentNumber
FROM  Table1,
(SELECT @sno := 000) t
;

The data in column stockCurrentNumber of table2 will have the data without leading zeros because the datatype is integer and 001 =1 so for the leading zeros where you fetch the results you can use lpad(stockCurrentNumber,3,'0') for more see LPAD Docs and for the query its not harder one i have just used the variable to generate auto incremented values for your stockCurrentNumber column and CONCAT() to combine to values like auto incremented value and the stockCode as CONCAT(stockCode,lpad(@sno + 1,3,'0')) stockCode,Hope it makes sense

Fiddle 2

Hi, Could you please explain this code to me. Also, how do I do it as an update statement? I tried `INSERT INTO Table1 VALUES ( CONCAT(`stockCode`,lpad(@sno + 1,3,0)) stockCode, `stockName`, `stockStart`, `stockFinish`, @sno := lpad(@sno + 1,3,0) AS stockCurrentNumbe`)` BUT I keep getting this error ` You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stockCode, `stockName`, `stockStart`, `stockFinish`, @sno := lpad(@sno + 1,3,0)' at line 2:` — user3138212, Mar 21 '14 at 00:06
Sorry, could you please help me modify your code such that it works on my updated database that I am currently testing please — user3138212, Mar 21 '14 at 00:31
@user3138212 You could just Select ........ INTO FROM .... Or do a nested Insert Into table2 SELECT ............... — Sky, Mar 21 '14 at 00:50
@user3138212 see my updated answer and [*updated fiddle*](http://sqlfiddle.com/#!2/29894/4) — M Khalid Junaid, Mar 21 '14 at 07:09

How to extract numbers?

1 Answers1

Fiddle

Fiddle 2