how to split a stiring in mysql

[sam023]

i have a data in mysql in this format..

1,2,3,4,5,6...

can i split and show this data in this format in mysql??
1
2
3
4
5
6

thanks ijn advance

[rajabhaskar525]

i have a data in mysql in this format..

1,2,3,4,5,6...

can i split and show this data in this format in mysql??
1
2
3
4
5
6

thanks ijn advance

print_r (explode(" ,",$str));

try this one. you get it.

[network18]

i have a data in mysql in this format..

1,2,3,4,5,6...

can i split and show this data in this format in mysql??
1
2
3
4
5
6

thanks ijn advance

in mysql use this -

SUBSTRING_INDEX(field,'-',1)

[diafol]

I wasn't aware of N18's method, but as for Raj's method - the explode function places the data into an array.

$nums = explode(" ,",$str);
foreach ($nums as $num) {
    echo $num . "<br />";
}

In case you need to format you values with html.

[sam023]

Actually friends i want to do it in DB only.. not at front end..!!

Network18 i used ur code..!! but it give only one output..!!

select SUBSTRING_INDEX(prifix,',',1) as prefix from member

can we count the value in a string in mysql.? if yes then we can do it in loop..

[diafol]

The '1', from the mysql manual, just returns one value before the delimiter. Unfortunately, when this is changed to another number, e.g. 3, you'll probably get "1,2,3" as output.

You need a function to place the numbers into an array which you can then concatenate with a newline character.

You could use the replace function?

SELECT REPLACE('1,2,3,4,5,6', ',', CHAR(13) + CHAR(10));

The above replaces the comma with a combination "new line" and "carriage return".

I don't know if mysql will accept "\n" or "\r\n" instead. Give it a go.