StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do I select noncontiguous characters from a string of text in MySQL?
text
Body
copied!<p>I have a table with millions of rows and a single column of text that is exactly 11,159 characters long. It looks like this:</p> <pre><code>1202012101...(to 11,159 characters) 1202020120... 0121210212... ... (to millions of rows) </code></pre> <p>I realize that I can use </p> <pre><code>SELECT SUBSTR(column,2,4) FROM table; </code></pre> <p>...if I wanted to pull out characters 2, 3, 4, and 5:</p> <pre><code>1202012101... 1202020120... 0121210212... ^^^^ </code></pre> <p>But I need to extract noncontiguous characters, e.g. characters 1,5,7:</p> <pre><code>1202012101... 1202020120... 0121210212... ^ ^ ^ </code></pre> <p>I realize this can be done with a query like:</p> <pre><code>SELECT CONCAT(SUBSTR(colm,1,1),SUBSTR(colm,5,1),SUBSTR(colm,7,1)) FROM table; </code></pre> <p>But this query gets very unwieldy to build for thousands of characters that I need to select. So for the first part of the question - how do I build a query that does something like this:</p> <pre><code>SELECT CHARACTERS(string,1,5,7) FROM table; </code></pre> <hr> <p>Furthermore, the indices of the characters I want to select are from a different table that looks something like this:</p> <pre><code>char_index keep_or_discard 1 keep 2 discard 3 discard 4 discard 5 keep 7 discard 8 keep 9 discard 10 discard </code></pre> <p>So for the second part of the question, how could I build a query to select specific characters from the first table based on whether keep_or_discard="keep" for that character's index in the second table?</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