如何把用逗号等字符隔开的字符串转换成列表,下面依逗号分隔符为例:
比如有一个字符串,其值为:香港,张家港,北京,上海
用SQL把这个字符串转换成列表的方法是:
1、方法一
WITH A AS (SELECT '香港,张家港,北京,上海' A FROM DUAL)
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))+1 C
FROM(
SELECT A,INSTR(A,',',1,LEVEL) B,LEVEL LV FROM A
CONNECT BY 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 FROM DUAL)
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))+1 C
FROM(SELECT A,INSTR(A,',',1,LEVEL) B,LEVEL LV FROM A
CONNECT BY LEVEL <=(LENGTH(A) - LENGTH(REPLACE(A,',','')))+1
)))
2、方法二:使用oracle regexp_substr中的正则表达式
WITH temp AS
(SELECT '香港,张家港,北京,上海,95,aa' text
FROM DUAL)
SELECT regexp_substr (text, '[^,]+', 1, rn) city
FROM temp t1,
(SELECT LEVEL rn
FROM DUAL
CONNECT BY LEVEL <=
(SELECT LENGTH (text)
- LENGTH (REPLACE (text, ',', ''))
+ 1
FROM temp)) t2
3、方法三:使用的表(FW_ANSWER)
select answer from fw_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)) answer
from fw_answer t1,(select rownum row_num from user_objects where rownum<= 10) t2
where 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) answer
from fw_answer t1,(select rownum row_num from user_objects where rownum<= 10) t2
where 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以后才可以。
表结构:
1 A
1 B
1 C
2 A
2 B
3 C
3 F
4 D
转换后变成:
1 A,B,C
2 A,B
3 C,F
4 D
方法:
假设你的表结构是tb_name(id, remark),则语句如下:
SELECT a.id, wm_concat (a.remark) new_result FROM tb_name a group by a.id
分享到:
相关推荐
SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串SQL截取以逗号分隔的字符串
SQLServer逗号分隔的字符串转换成表
将带分隔符的字符串 例如 '1,2,3,4,5,6,7,8,9,10' 转换成table 的列的函数
sqlserver中根据某个字符切割字符串函数,比如根据逗号切割字符串,结果返回的是一个表值函数,这个函数返回结果字段包含Id和Value,Id为序号,Value为切割后的值
在做项目时,经常会遇到这样的表结构在主表的中有一列保存的是用逗号隔开ID
您可能感兴趣的文章:sql语句中如何将datetime格式的日期转换为yy-mm-dd格式将WMI中的DateTime类型转换成VBS时间的函数代码LINQ字符串向datetime 转换时失败的处理方法Sql中将datetime转换成字符串的
用 逗号等分隔符 分开的字符串数量 比如 aaa,bbb,ccc 输出3
将sql sqlser 日期类型转化成各种格式的字符串。
sql server 2008 将一列值转换成一个字符串
SQLServer的实用语句,能把一些字符串转换成数据表,传入两个参数:字符串、用于分割的单字节,一般是逗号‘,’
sql-varbinary转换成字符串,主要是将SQL中的二进制转换成字符串显示。
sql 实例 批量转换 字符串转换日期
SQL字符串函数SQL字符串函数SQL字符串函数SQL字符串函数SQL字符串函数SQL字符串函数SQL字符串函数SQL字符串函数
SqlServer时间日期处理函数及字符串转换 SqlServer时间日期处理函数及字符串转换 文章分类:数据库 最近做的报表,用SqlServer2005,边做边学吧,虽然速度有些慢~
SQL截取字符串函数(1、截取从字符串左边开始N个字符 2、截取从字符串右边开始N个字符(例如取字符www.163.com) 3、截取字符串中任意位置及长度 等等)
找了网上很多资料,都没有详细的做法有的还很复杂,自己写了个分割函数,通过直接查询就能分割多行,很方便。
自己写了一个function 用于处理拆分时的逻辑运算,性能比网上找的快好几倍, 代码和说明:https://blog.csdn.net/jimyao37/article/details/123522885
SQL 字符串处理函数 获取指定的字符
主要记录在工作中,初识db2遇到的一些问题 db2字符串分隔,函数,过程的使用
用SQL语句截取字符串, string substr ( string string, int start [, int length]) 参数1:处理字符串 参数2:截取的起始位置(第一个字符是从0开始) 参数3:截取的字符数量