asked on

How to find unique values within a string array that contains semi-colon delimited values?

I'm using PHP and MySQL to power a small application. I'm importing data from an .ods file. The import produces ugly string values delimited by semi-colons. There are less than 1000 rows of data within the resulting table.

Example:

"SELECT DISTINCT submissions.tags FROM submissions";

#quality;#RIE;#PDSA;#6S;#MyEvent;
#quality;#RIE;#PDSA;#teamengagement;#6S;

I'm looking to extract the unique values beginning with the # character. An example of the resulting unique array would look like:

#quality;#RIE;#PDSA;#6S;#MyEvent;#teamengagement;

I'm wondering if this can be accomplished using strictly SQL or, if this would best be accomplished using PHP?

In either instance, I'm not knowledgeable of how to go about accomplishing this task?

Any guidance would be appreciated. Cheers!

Scott Fell

If you don't need to use the individual items in your query, it is better to do this on the server.

$str = "#quality;#RIE;#PDSA;#6S;#MyEvent;#teamengagement;";


print_r (explode("#",$str));

Open in new window

If you want to remove the the semicolon at the end of each then

$str = "#quality;#RIE;#PDSA;#6S;#MyEvent;#teamengagement;";

$str = str_replace(";","",$str);

print_r (explode("#",$str));

Open in new window

A usage example

$str = "#quality;#RIE;#PDSA;#6S;#MyEvent;#teamengagement;";

$str = str_replace(";","",$str);

$array =  explode("#",$str);

echo "MyEvent= ".$array[5];

Open in new window

evibesmusic

ASKER

Hi Scott,

Thank you. You mention attempting to accomplish this server-side, so I assume you mean using SQL?

If not, using PHP and your examples I would:

- Create an empty array which will hold all values
- Clean up each semi-colon delineated array found in the DB and, add these values to the empty array
- Use the array_unique() function to find the unique values within the newly constructed array

This doesn't seem like the most expeditious process but, I believe it would work.

???

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Fell

I was using php

SOLUTION

Ionut A. Tudor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

theGhost_k8

There is this procedure to convert delimited string to rowswhich is quiet similar functionally as stated in few posts back but worth trying. You may call the procedure and execute next part on those generated tables.

PortletPaul

>>"Accomplish it using SQL is troublesome and hard to update the code if the format ever changes."

I suggest it is no harder to update the SQL code that it is to update the PHP code, e.g. for a different table or column change the bold seen below.

select
id
, SUBSTRING_INDEX(SUBSTRING_INDEX(submissions.tags, ';', numbers.n), ';', -1) tag
from numbers
inner join submissions
on CHAR_LENGTH(submissions.tags)
-CHAR_LENGTH(REPLACE(submissions.tags, ';', '')) >= numbers.n-1
order by
id, n

i.e. the structure of the SQL does not alter at all, just the relevant facts need updates

nb: If the delimiter changes there are 3 references to it ';' needs to become ',' for example

There is a one-time setup of a numbers table required - but this has a bundle of other uses as well.

evibesmusic

ASKER

Thank you, Experts.

I learned several new techniques including the creation of a numbers table which I had not considered - and - new PHP syntax that I can use to create a custom function.

I appreciate the different approaches and will use both during testing to determine which methodology provides the best performance for my application.

Cheers.