Extract substring using SQL

mlambert

Hey guys,

Was wondering if anyone can help? I have a single field in a database which is actually a string of strings delimited by a comma, e.g.:

$alternativeNames = "name1, name2, name3, name4" ;

If was wondering if there was anyway to individually extract the substrings for comparison or output.

For example, is there any way using an SQL statement that I can extract the row that will has the substring 'name3' in the alternativeNames column?

I am trying not to have to return all results from the database and then perform explode/tokenizer functions on the strings to produce results as per the memory/performance overhead.

Pig

Your database format is not normalized. You should alter your table structure.

Create a table for a many-to-many relationship.

instead of:
John | 2, 5, 3
Your table should look like:
John | 2
John | 5
John | 3

Then you can do a query for everything with a column 1 value of John.

This will also allow a lot more flexibility in what you can do with the data.

mlambert

The DB actually is normalised - alternativeName is an attribute of an entity (even though I know if an attribute has attributes, it is supposed to be an entity). I just lost my brain and was trying to find an easier way than having to use another table and indexing to base my search from id's, etc, but you're probably right, looks like I'm gonna have to normalise the design more. Thanks for your help.