1

I have a table with "content" column store forum post, there is one or more url in one record of "content" field, I want to get all the url in the “content" column, one url in one row, I use below code

select substr(`content`, locate(`content`,"http://"))

it work for one url in one record, get a list of url like

it only get the first url if there are more than one url in the record.

how to fix it?

eagle
  • 17
  • 3
  • Sure would be easier if the data was normalized so that values were stored independently of one another... – David Mar 17 '16 at 13:25
  • This can help: http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows but the way to go is as @David said above - normalize the data first. – ptrk Mar 17 '16 at 13:34
  • it's normal some posts has more than one urls, like reply some newbie's question, some users will reply will some links, so there are 2 or 3 links in one post. – eagle Mar 17 '16 at 13:38
  • I'm new to mysql but I read that post the text in the sample is seperated by comma, in my case I think I can identify the url by "http://" or – eagle Mar 17 '16 at 13:49

1 Answers1

0

Another way to look at it is to try:

SELECT GROUP_CONCAT(substr(`content`, locate(`content`,"http://"))) FROM your_table;

which would concatenate all URLs to a single string and carry on from there - maybe you can split it in the code rather than require the DB to do it. Otherwise you can hack on using an auxiliary table of integers 1-n: SQL split comma separated row

Community
  • 1
  • 1
ptrk
  • 1,800
  • 1
  • 15
  • 24