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.

As @Devart suggests you should think of changing the design to further normalize it. This will be helpful for you. — chandimak
– chandimak, Commented Feb 26, 2013 at 8:07

Devart · Accepted Answer · 2013-02-26 07:22:48Z

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.

answered Feb 26, 2013 at 7:22

Devart

120k23 gold badges171 silver badges189 bronze badges

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

1 Comment

Jey Over a year ago

your solution works fine in mysql. Thanks. By chance do you know how to do it for teradata?

Collectives™ on Stack Overflow

SQL loop in select

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related