找到文本位置,提取文本并在MySQL的新列中插入 [英] Locate text position, extract text and insert in new column in MySQL
本文介绍了找到文本位置,提取文本并在MySQL的新列中插入的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在MySQl表中有以下行示例
I have the following example of rows in a MySQl table
Column A
Row1 Lauguage=English&Country=USA&Gender=Male
Row2 Gender=Female&Language=French&Country=
Row3 Country=Canada&Gender=&Language=English
如何实现以下目标:例如,我需要查找国家/地区
How can I achieve the following: For example, I need to look for Country
- 我需要在此文本列中找到国家/地区"的位置.这逐行变化.
- 然后我需要忽略参数"Country =",而仅提取值.在某些情况下,这将为NULL(如Row2中的示例),而在某些行中,我需要后面跟有'='的值(如Row1& Row3中的示例).但是,我需要确保仅获得价值.不是下一个由'&' 分隔的参数
- 一旦提取了Country参数的值,我需要创建一个新列,现在将在其中提取和存储这些值.
最终结果:新列
Column B
Row1 USA
Row2
Row3 Canada
这里的任何帮助将不胜感激!谢谢!
Any help here would be appreciated! Thanks!
推荐答案
您可以选择"Country ="之后的文本,然后在拥有该子字符串之后,选择第一个&"之前的文本
You can pick the text following the 'Country=', and then once you have that substring, pick the text before the first '&'
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ColumnA, 'Country=', -1), '&', 1) AS ColumnB
FROM `atable`
请参见 http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_substring-index
这是测试以证明:
mysql> SELECT * FROM atable;
+------+------------------------------------------+
| row | columna |
+------+------------------------------------------+
| Row1 | Lauguage=English&Country=USA&Gender=Male |
| Row2 | Gender=Female&Language=French&Country= |
| Row3 | Country=Canada&Gender=&Language=English |
+------+------------------------------------------+
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ColumnA, 'Country=', -1), '&', 1) AS ColumnB FROM atable;
+---------+
| ColumnB |
+---------+
| USA |
| |
| Canada |
+---------+
关于您的后续问题:
Re your followup question:
INSERT INTO atable VALUES ('Row4', 'Gender=&Language=English');
SELECT `row`, IF(LOCATE('Country=', ColumnA)>0,
COALESCE(
NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(ColumnA, 'Country=', -1), '&', 1), ''),
'Blank string is not valid!'),
'Missing Country!') AS ColumnB
FROM `atable`
+------+----------------------------+
| row | ColumnB |
+------+----------------------------+
| Row1 | USA |
| Row2 | Blank string is not valid! |
| Row3 | Canada |
| Row4 | Missing Country! |
+------+----------------------------+
这篇关于找到文本位置,提取文本并在MySQL的新列中插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文