Extract multiple values from one column in MySql_问答_开发者

Extract multiple values from one column in MySql

开发者 https://www.devze.com 2023-03-15 11:53 出处：网络

I\'ve noticed that MySql has an extensive search capacity, allowing both wildcards and regular expressions.However, I\'m in somewhat in a bind since I\'m trying to extract multiple values from a singl

相关专题：regex sql

I've noticed that MySql has an extensive search capacity, allowing both wildcards and regular expressions. However, I'm in somewhat in a bind since I'm trying to extract multiple values from a single string in my select query.

For example, if I had the text "<span>Test</span> this <span>query</span>", perhaps using regular expressions I could find and extract values "T开发者_开发技巧est" or "query", but in my case, I have potentially n such strings to extract. And since I can't define n columns in my select statement, that means I'm stuck.

Is there anyway I could have a list of values (ideally separated by commas) of any text contained with span tags?

In other words, if I ran this query, I would get "Test,query" as the value of spanlist:

select <insert logic here> as spanlist from HtmlPages ...

MySQL does have a regular expression engine, but it is implemented in the WHERE clause, not as part of the query field. It therefore cannot be used to split a string.

I would suggest that you'll need to use another language for this. Query the data using an application and split it as required there, or write a small program to do it - it'd probably be just a few lines in PHP^‡.

If you really want to do it in MySQL, you could probably write a function to do it -- see this answer for some leads: Can Mysql Split a column? -- but frankly, it's not what MySQL (or SQL in general) is designed to do. You'd be much better off querying the data normally and splitting it into bits in another language.

(^‡ I mention PHP as it's what MediaWiki is written with, so you should be able to run PHP code without installing anything new, but pretty much any language will do).

First of all - your data structure is miserable. One of the most common database rules is "Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else)." It's one of the elementary rules when developing data structure.

As you see there is no possibility to write static select statement returning variable count of columns. The best approach if you do not want to change structure is to get text as is and post-process it in your favorite programming language.

I'm surprised no one suggested EXTRACTVALUE(xml,'xpath') I think it does exactly what you are asking for, only with a little bit of trickery to get that delimiter in there (default "delimiter" is a space).

SET @xml = '<html><span>Test</span> this <span>query</span>
    <span>etc</span><div><span>etc etc</span></div></html>';

SELECT 
    LEFT(spanlist,LENGTH(spanlist)-1) AS spanlist
FROM
    (SELECT 
        EXTRACTVALUE(REPLACE(@xml,'</span>',',</span>'),'//span') AS spanlist
    ) AS T
;

Produces

+---------------------------+
| spanlist                  |
+---------------------------+
| Test, query, etc, etc etc |
+---------------------------+

Sorry this answer may be too late for you but hope it helps the next person

Unfortunately the only solution available was to use a third party text editor such as Ultraedit and remove the tags using regular expressions. It seems it's not possible to use MySql.

Though this is not an idealic solution. If anyone ever stumbles across this question with a solution that will allow me to use MySQL to extract this information multiple lines or otherwise, I'd be more than happy to hear it.

Lacking a better solution, this will have to answer my question for now.