StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The source of your difficulty is that your schema does not represent the true relationships between the data elements. If you wanted to achieve this with "pure" SQL, you would need a schema more like:</p> <pre><code>table ID Index Char 1 0 1 1 1 2 1 2 0 charsToKeep ID Index Keep 1 0 false 1 1 true 1 2 true </code></pre> <p>Then, you could perform a query like:</p> <pre><code>SELECT Char FROM table t JOIN charsToKeep c ON t.ID = c.ID WHERE c.Keep = true </code></pre> <p>However, you probably have good reasons for structuring your data the way you have (my schema requires much more storage space per character and the processing time is also probably much longer from what I am about to suggest).</p> <p>Since SQL does not have the tools to understand the schema you have embedded into your table, you will need to add them with a user-defined function. Kevin's example of dynamic SQL may also work, but in my experience this is not as fast as a user-defined function.</p> <p>I have done this in MS SQL many times, but never in MySql. You basically need a function, written in C or C++, that takes a comma-delimited list of the indexes you want to extract, and the string from which you want to extract them from. Then, the function will return a comma-delimited list of those extracted values. See these links for a good starting point:</p> <p><a href="http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html</a></p> <p><a href="http://dev.mysql.com/doc/refman/5.1/en/adding-udf.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/adding-udf.html</a></p> <p>To build the concatenated list of indexes you want to extract from the char_index table, try the group_concat function:</p> <p><a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat</a></p> <p>Hope this helps!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload