GROUP BY Regex

[e1seix]

I've got one for you guys.

 

If I have a select statement to search a table in mysql. I want to group by PART of a column data... the data being a URL.

 

Row 1, Column value: "www.mysite.com/index.php?id=1&code=ABC"
Row 2, Column value: "www.mysite.com/index.php?id=1&code=BCD"
Row 3, Column value: "www.mysite.com/index.php?id=2&code=CDE"
Row 4, Column value: "www.mysite.com/index.php?id=3&code=DEF"
Row 5, Column value: "www.mysite.com/index.php?id=5&code=EFG"
Row 6, Column value: "www.mysite.com/index.php?id=5&code=FGH"

 

So I would like to use a select statement to group the "id" value of the Column, whereby it will return the results... 1,2,3,5.

 

Can it be done?

[salathe]

Can it be done? Sure, not with REGEXP though (you'd need to use string functions).  However, you'd be much better off extracting that ID number and storing it in another column which can be indexed.

[e1seix]

Can it be done? Sure, not with REGEXP though (you'd need to use string functions).  However, you'd be much better off extracting that ID number and storing it in another column which can be indexed.

 

How would one do this? ie. The extraction part...

[fenway]

You can use SUBSTRING_INDEX() or LOCATE(), but only if you know *exactly* how all of your data is formatted.

[e1seix]

Question:

 

If I'm using one of these...

 

how for example would you code it if you wanted to extract the value of "id" from the following rows that contains a url in the column "URL"

 

row 1, column value: "www.mysite.com?id=33&name=Brenda"

row 2, column value: "www.mysite.com?id=120&name=Bradley"

row 3, column value: "www.mysite.com?id=1023&name=Bobby"

 

What way is the code constructed so that for each loop of the statement, I get the value of 33, 120 & 1023 to define as a variable and implement into a different column of my database separately.

 

All help appreciated.

[fenway]

Like so:

 

select substring_index( trim( "www.mysite.com?id=" from "www.mysite.com?id=33&name=Brenda" ), '&', 1 )

[zeodragonzord]

You can so something like:

 

SELECT field1, field2 AS myAlias FROM tableName GROUP BY myAlias;

 

You'll need to extract the ID from field2 and create an alias for it to be used in the GROUP BY clause.  Yes, it's better to have that ID in a separate column.  Perhaps you can create a TRIGGER that will insert the ID into an ID column whenever a new row is inserted into this table.  If not, have your PHP code parse it out and insert the ID that way.