如果比较Column_1 中的字符存在于Column_2 例如
Select 'A,B' AS Column_1 ,'B,C,D' AS Column_2
union
Select '12,45' AS Column_1 ,'B,123' AS Column_2
union
Select '12,45' AS Column_1 ,'B,123,45' AS Column_2;我要的结果是 Select 'A,B' AS Column_1 ,'B,C,D' AS Column_2 ,1 AS result
union
Select '12,45' AS Column_1 ,'B,123' AS Column_2 ,0 AS result
union
Select '12,45' AS Column_1 ,'B,123,45' AS Column_2 ,1 AS result;也就是说. Column_1只要有一个字符在Column_2 中, result 就为 1,否则为0
Select 'A,B' AS Column_1 ,'B,C,D' AS Column_2
union
Select '12,45' AS Column_1 ,'B,123' AS Column_2
union
Select '12,45' AS Column_1 ,'B,123,45' AS Column_2;我要的结果是 Select 'A,B' AS Column_1 ,'B,C,D' AS Column_2 ,1 AS result
union
Select '12,45' AS Column_1 ,'B,123' AS Column_2 ,0 AS result
union
Select '12,45' AS Column_1 ,'B,123,45' AS Column_2 ,1 AS result;也就是说. Column_1只要有一个字符在Column_2 中, result 就为 1,否则为0
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/01/4141864.aspx
-> concat(',',Column_2,',') regexp concat(',(',replace(Column_1,',','|'),'),') AS result
-> from
-> (
-> Select 'A,B' AS Column_1 ,'B,C,D' AS Column_2
-> union
-> Select '12,45' AS Column_1 ,'B,123' AS Column_2
-> union
-> Select '12,45' AS Column_1 ,'B,123,45' AS Column_2
-> ) t;
+----------+----------+--------+
| Column_1 | Column_2 | result |
+----------+----------+--------+
| A,B | B,C,D | 1 |
| 12,45 | B,123 | 0 |
| 12,45 | B,123,45 | 1 |
+----------+----------+--------+
3 rows in set (0.00 sec)mysql>