I'm trying to select names out of a database. The names are companies with parent and if applicable child accounts like this:

parent_a
parent_b
parent_b:child_b1
parent_b:child_b2
parent_c:child_c1

What I want to select out is unique values before the colon delimiter so my query on the above rows would select:

parent_a
parent_b
parent_c

The issue is arising from the fact that some companies have only a parent (no delimiter as in parent_a above) so when I am trying to select the string before the delimiter, there isn't a delimiter and doesn't return the row at all.

I'm trying to accomplish this strictly through MySQL but if necessary I could pull out all rows and filter the results through PHP.

    Are the values literally stored in a single column in that format? If so, why? It would be a lot easier to use SQL if your data was structured/normalized properly in the first place.

      It's not my database design but perhaps I should try to get that changed that rather than work around it. Still, if there is a solution to this problem, it would be helpful.

        You can do it, yes, but it's probably going to get ugly.

        For example, you could use an IF() statement that uses INSTR() to check if ':' is in the string value. If it is, then use LEFT() to return the left-hand part of the string up until the colon. If it isn't, then simply return the string unchanged.

          In other words, until we get the DB fixed then do it in PHP.

            Write a Reply...