mysql如何把字符串转为列表_使用SQL如何把用逗号等字符隔开的字符串转换成列表,以及把列合并成符合隔开的字符串(转)...

如何把用逗号等字符隔开的字符串转换成列表,下面依逗号分隔符为例:

比如有一个字符串,其值为:香港,张家港,北京,上海

用SQL把这个字符串转换成列表的方法是:

1、方法一

WITH A AS (SELECT '香港,张家港,北京,上海' A FROMDUAL)SELECT DECODE(B,0,SUBSTR(A,C),SUBSTR(A,C,B-C)) city FROM(SELECT A,B,(LAG(B,1,0) OVER(ORDER BY LV))+1CFROM(SELECT A,INSTR(A,',',1,LEVEL) B,LEVEL LV FROMA

CONNECTBY LEVEL <=(LENGTH(A) - LENGTH(REPLACE(A,',','')))+1)

)

输出结果是:

香港

张家港

北京

上海

应用举例:

如果table1表的city字段的值为:北京;table2表的city字段的值为:香港,张家港,北京,上海

要想用city字段关联table1,table2表来查询table1表中的数据,首先我们会想到用(例:select * from table1 where field in (select field from table2))方式来查询,但是这样查询的结果却不正确,仔细观察会发现如果用in时,table2表的city字段的值必须得是('香港','张家港','北京','上海')格式,这样查询的结果才会正确,这时如果我们使用下面的SQL就可帮我们解决这个问题了。

例:select * from table where field in(WITH A AS (SELECT (select field from table2) A FROMDUAL)SELECT DECODE(B,0,SUBSTR(A,C),SUBSTR(A,C,B-C)) city FROM(SELECT A,B,(LAG(B,1,0) OVER(ORDER BY LV))+1CFROM(SELECT A,INSTR(A,',',1,LEVEL) B,LEVEL LV FROMA

CONNECTBY LEVEL <=(LENGTH(A) - LENGTH(REPLACE(A,',','')))+1)))

2、方法二:使用Oracle regexp_substr中的正则表达式

WITH temp AS(SELECT '香港,张家港,北京,上海,95,aa' text

FROMDUAL)SELECT regexp_substr (text, '[^,]+', 1, rn) cityFROM tempt1,

(SELECT LEVELrnFROMDUAL

CONNECTBY LEVEL <=(SELECT LENGTH (text)- LENGTH (REPLACE (text, ',', ''))+ 1

FROM temp)) t2

3、方法三:使用的表(FW_ANSWER)

select answer fromfw_answer

ANSWER-----------------

A,B,C

A,F

B,D,E

D

要把逗号分隔的转列换成行显示,这里使用了substr的方式,如下:

select substr(answer,instr(','||answer|| ',', ',', 1, t2.row_num),

instr(','||answer|| ',', ',', 1, t2.row_num+1)-1-instr(','||answer, ',', 1, t2.row_num)) answerfrom fw_answer t1,(select rownum row_num from user_objects where rownum<= 10) t2where nvl(substr(answer,instr(','||answer||',', ',', 1, t2.row_num),

instr(','||answer||',', ',', 1, t2.row_num+1)-1-instr(','||answer, ',', 1, t2.row_num)),'-')!='-'

order by answer

查询结果:

ANSWER-----------------

A

A

B

B

C

D

D

E

F

【如果是使用其他字符分隔的,以上方式也可以,只需要将有逗号的地方换成该字符。】

以上方式是针对字符存储无规律的情况,对于fw_answer表中的答案列是有规律可循的,所以简化后的sql如下:

select substr(answer,t2.row_num*2-1,1) answerfrom fw_answer t1,(select rownum row_num from user_objects where rownum<= 10) t2where nvl(substr(answer,t2.row_num*2-1,1),'-')!='-'

order by answer

【注:user_objects主要描述当前用户通过DDL建立的所有对象。包括表、视图、索引、存储过程、触发器、

包、索引、序列等。是oracle字典表的视图。这里也可以通过其它方式,如dual,此处为了免去content by

所以不用dual,用user_objects。】

------------------------------------------------------------------------------------------------------

正好相反的操作:把列转换成行

从网上找了都是关于decode的方法实现的列转行,后来发现了用orcale的wmsys.wm_concat方法可以轻松的实现,下面的范例是网上找的:wmsys.wm_concat要10g以后才可以。

表结构:1A1B1C2A2B3C3F4D

转换后变成:1A,B,C2A,B3C,F4 D

方法:

假设你的表结构是tb_name(id, remark),则语句如下:

SELECT a.id, wm_concat (a.remark) new_result FROM tb_name a group bya.id

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值