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

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.

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