Splitting strings and joining the duplicates

Question

I'm trying to create a select box drop down with a twist,

Basically this is an Ajax form that when an item is selected from the list it'll add it into a text field. However I also want to add a few extra selections in here.

the string I'm getting is made up of COMPANY_SITE_DEPARTMENT and for example SDGCC_NEWTOWN_INBOUND.

Using PHP I wish to take every item from the database in the logintags table which could contain multiple COMPANY_ so anything that states SDGCC I want the drop down box to have the special selection for adding all SDGCC sites if that makes sense? I've tried it but I get duplicates if there is more than one row that contains the SDGCC tag.

duplicates :

SDGCC_NEWTOWN_INBOUND
SDGCC_NEWTOWN_QH
SDGCC_BOLTON_QH
ARISE_HOME_ORDERS
ARISE_HOME_ENQUIRIES

etc...

So basically it'd have an option to select all SDGCC sites but because that database could change at any point we only need the first part of the string (ie the SDGCC, ARISE) sections to show up (once) in the select box.

I'm currently trying suggested query from below

SELECT DISTINCT SUBSTRING( tag, 0, LOCATE('_', tag ) ) FROM dept_logintags LIMIT 0 , 30

However this returns no rows

Answer 1

Don't reinvent the wheel

Basically what you want is a distinct on the COMPANY_ part of your logintags.

Why don't you read data from your DB as DISTINCT? You can easily make a DISTINCT on the first part of the string value by using an expression column in your query that strips out the first part. So you wouldn't need any string manipulation on the middle tier at all?

Example for Microsoft SQL

// first part
SELECT DISTINCT SUBSTRING(tag, 0, CHARINDEX(tag, '_')) FROM dept_logintags
// second part
SELECT DISTINCT SUBSTRING(SUBSTRING(tag, CHARINDEX(tag, '_') + 1, LEN(tag)), 0, CHARINDEX(tag, '_')) from dept_logintagss
// last part
SELECT DISTINCT SUBSTRING(SUBSTRING(tag, CHARINDEX(tag, '_') + 1, LEN(tag)), CHARINDEX(tag, '_') + 1, LEN(tag)) from dept_logintags

Because in the end you would like to do the same thing in PHP.

Example for MySQL

In MySQL you can even use SUBSTRING_INDEX function to simplify selects even further

// first part
SELECT DISTINCT SUBSTRING_INDEX(tag, '_', 1) FROM dept_logintags
// second part
SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(tag, '_', 2), '_', -1) from dept_logintags
// last part
SELECT DISTINCT SUBSTRING_INDEX(tag, '_', -1) FROM dept_logintags