data splitting

smeagal

hi all...

just wanna know can i 'explode' data in a mysql table like php? here an example in php

$data = "A,B,C,D";

$dataArray = explode(",",$data);

//then it will be like
//$dataArray[0] = "A";
//$dataArray[1] = "B";
//$dataArray[2] = "C";
//$dataArray[3] = "D";

this is because i want to put data in mysql in $data kind of format. when i want to do a search string, i will just 'explode' the data and do the necessary query.

the purpose i'm doing this is because i want to store few id in one single column. so when i want to search for a certain id, i will do something like above. i tried substring(), but those id might expand more and i have to edit my code.

eg:

myTable
|-----------------|
| mixedId |
|-----------------|
| 031290 |
|-----------------|
| 102234 |
|-----------------|

select mixedId from myTable where substring( mixedId, 3, 2 ) = '22'

the number you see in column mixedId represents few id. the first two digits are a foreign key lets say to table A, next two digits for table B and next two is for table C. those id are incremental numbers. what concerns me if one of the id will add up to 100, then the query result will be so messed up. so that's why i want to put a separator between those id and no mess will happen.

so got any explode for me?

thanx

bad76

Hi,
the function SUBSTRING_INDEX(str,delim,count) is what you are searching for.

But my personal hint is to use LIKE in the where clause...
ie: separator='.';
mixedId = 3.12.190

select mixedId from myTable
where mixedid LIKE '%.22.%'

select mixedId from myTable
where mixedid LIKE '100.%.%'

...

I hope this helps.

Pig

Your database structure is not normalized. The cleanest way would be to create a many-to-many table that has an entry for component of $data.

example:
1 | 3
1 | 12
1 | 190

Then you can do a simple join to get all the data.

smeagal

hi,

i will certainly try the SUBSTRING_INDEX(str,delim,count). see how it goes.

actually the wise guy behind this thing is my boss. don't know why. i'm just following orders. i would rather doing it by relation between table than mixed ids. well, he's da boss.

thanx guys!

edit: sorry for my late reply, i was on holiday... :p