retrieve data where field contains delimiter

I have been thinking about this for hours, and I cant quite get the
correct keyword for me to search it in the manual.

for example i have a field that contains string1:string2:string3
where colon ':' is the delimiter.

How do i create a select statement that only returns string2?


Thank you all in advance.

 

only with mysql I dont know but you can use explode() function in php to extract from db

 

There isn't any easy way to do it from a database level. From an application level you can explode or use another function (depending on the language) to break out of the delimitation.

You shouldn't store data this way if you don't have to because of this exact reason. Ideally you want a separate table with a common id field that tied it back to the main row you are dealing with.

 

Hi,
of course it is possible, but not sure how fast this query is performed. Also it's not as easy to implement as on the PHP or any server side language. But just in the case you still want to go that way :

SELECT SUBSTRING(your_col, LOCATE(':', your_col)+1, LOCATE(':', your_col, LOCATE(':', your_col))-1) FROM your_table WHERE your_id=value LIMIT 1;

Just replace your_col, your_table and if you're using some WHERE clause, your_id as well...

 

basically, what i need is, a multiple categories. I am developing a video channel. So, if the video belongs to multiple categories, how will you place that data in the table