SQL count query number of lines per row

Question

I am trying to count the amount of urls we have in field in sql I have googled but cannot find anything !

So for example this could be in field "url" row 1 / id 1

url/32432
url/32434

So for example this could be field "url" in row 2 / id 2

url/32432
url/32488
url/32477

So if you were to run the query the count would be 5. There is no comma in between them, only space.

Kind Regards

Scott

Possible duplicate of [Equivalent of explode() to work with strings in MySQL](http://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql) — LordNeo, Mar 08 '17 at 15:24
you need a simil to "explode" function in php, check the post flagged as duplicate — LordNeo, Mar 08 '17 at 15:25

score 0 · Answer 1 · answered Mar 08 '17 at 15:25

0

This is a very bad layout for data. If you have multiple urls per id, then they should be stored as separate rows in another table.

But, sometimes we are stuck with other people's bad design decisions. You can do something like this:

select (length(replace(urls, 'url', 'urlx')) - length(urls)) as num_urls

Note that the specific functions for length() and replace() might vary, depending on the database.

answered Mar 08 '17 at 15:25

Gordon Linoff

Hi Gordon, so its a Jira table i am querying. Basically each time someone calls us, we open a new internal ticket ( not jira ticket ) if this ticket is related to a bug ( Jira ticket ) we then link it to this jira ticket, so one row ie linked_tickets field could have many many urls in it. I am not sure if the query you gave me is what i am looking for ? – Scott Robins Mar 08 '17 at 15:34
@ScottRobins . . . You should fix your database so each new link is a new *row* in a table, rather than just appending a new value to an existing column. – Gordon Linoff Mar 09 '17 at 03:01

1 Answers1