SQL loop in select

Question

Need to update a column in our database. A column which can have upto 7 sets of numbers separated by a space between them. like

3548 132 760 2348
123 346
37 982 439 

I need to update these columns like the following.

3548 132 760 2348 0 0 0 
123 346 0 0 0 0 0
37 982 439 0 0 0

i.e like something to full fill the 7 sets of the number. change the 4 sets to 7 sets.

I could able to count the number of sets in the column by the following

SELECT LENGTH('3548 132 760 2348')-LENGTH(REPLACE('3548 132 760 2348', ' ', '')) + 1

Which returns the number of sets in the column. What SQL query will help to update or return the value as given above.

Answer 1

You could use this query -

SET @str = '548 132 760 2348';
SELECT RPAD(@str, LENGTH(@str) + (6 - (LENGTH(@str) - LENGTH(REPLACE(@str, ' ', '')))) * 2,' 0');

>548 132 760 2348 0 0 0

But if these are ID values, then I'd suggest you to normalize data.