字段中字符串切割问题

lixizheng 2017-01-01 04:07:11
数据库结构如下：


id part
1 225100:02:3:20160725;225100:01:20:20160601
2 225100:03:1:20160701
3 225200:02:12:20160501
4 :01:3:20160725
5 225200:01:2:20160401
6 225200:03::20160411
7 225300:02:3:20160725
8 225300:02:14:
9 225300:01:3:20160201
10 225300:01:3:20160301

-------------------------------------------------
请问该如何写sql，才能达到这样的效果：

id part1 part2
1 225100:02:3:20160725 225100:01:20:20160601
2 225100:03:1:20160701
3 225200:02:12:20160501
4 :01:3:20160725
5 225200:01:2:20160401
6 225200:03::20160411
7 225300:02:3:20160725
8 225300:02:14:
9 225300:01:3:20160201
10 225300:01:3:20160301

或者如果能达到这样的效果更好：
id a b c d
1 225100 02 3 20160725
1 225100 01 20 20160601
2 225100 03 1 20160701
3 225200 02 12 20160501
4 01 3 20160725
5 225200 01 2 20160401
6 225200 03 20160411
7 225300 02 3 20160725
8 225300 02 14
9 225300 01 3 20160201
10 225300 01 3 20160301


是SQLLite数据库，有些方法或功能可能实现不了，请大神帮帮忙，实在不行，就只实现part1 part2这样的效果也行。
...全文
605 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
道素 2017-01-03
  • 打赏
  • 举报
 回复
引用 8 楼 lixizheng 的回复:
我开始没注意，您给的是可以正常运行的，太感谢了！！！
我就是用SqlLite测试的，否则不需要 WITH RECURSIVE cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<100) 生成数字序列，SQL Server本身有master..spt_values 如果你不想这么写，自己建个表也可以，里面就保存1,2,3....这样的序列，其他的作用和上面的cnt 一样
道素 2017-01-02
  • 打赏
  • 举报
 回复
我把你的代码也结合进来，并找了一个由三部分组成的数据，你看看 

insert into chq3g4(id,part)
  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all
  select 2,'225100:03:1:20160701;225300:08:80:20160801;225400:09:90:20160901' union all
  select 3,'225200:02:12:20160501' union all
  select 4,':01:3:20160725' union all
  select 5,'225200:01:2:20160401' union all
  select 6,'225200:03::20160411' union all
  select 7,'225300:02:3:20160725' union all
  select 8,'225300:02:14:' union all
  select 9,'225300:01:3:20160201' union all
  select 10,'225300:01:3:20160301'

WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<100)

SELECT ID,part,
substr(part,1,(INSTR(part,':')-1))AS 地区,
substr(substr(part,INSTR(part,':')+1),1,instr(substr(part,INSTR(part,':')+1),':')-1) AS 分类,
substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),1,instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')-1) AS 数量,
substr(substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')+1),1,8)AS 日期
FROM (
	select id,  substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')) as part
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)
) as t

/*
ID	地区	分类	数量	日期
1	225100	02	3	20160725
1	225100	01	20	20160601
2	225100	03	1	20160701
2	225300	08	80	20160801
2	225400	09	90	20160901
3	225200	02	12	20160501
4		01	3	20160725
5	225200	01	2	20160401
6	225200	03		20160411
7	225300	02	3	20160725
8	225300	02	14	
9	225300	01	3	20160201
10	225300	01	3	20160301
*/
lixizheng 2017-01-02
  • 打赏
  • 举报
 回复
我开始没注意，您给的是可以正常运行的，太感谢了！！！
lixizheng 2017-01-02
  • 打赏
  • 举报
 回复
引用 5 楼 ch21st 的回复:
我把你的代码也结合进来，并找了一个由三部分组成的数据，你看看 

insert into chq3g4(id,part)
  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all
  select 2,'225100:03:1:20160701;225300:08:80:20160801;225400:09:90:20160901' union all
  select 3,'225200:02:12:20160501' union all
  select 4,':01:3:20160725' union all
  select 5,'225200:01:2:20160401' union all
  select 6,'225200:03::20160411' union all
  select 7,'225300:02:3:20160725' union all
  select 8,'225300:02:14:' union all
  select 9,'225300:01:3:20160201' union all
  select 10,'225300:01:3:20160301'

WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<100)

SELECT ID,part,
substr(part,1,(INSTR(part,':')-1))AS 地区,
substr(substr(part,INSTR(part,':')+1),1,instr(substr(part,INSTR(part,':')+1),':')-1) AS 分类,
substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),1,instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')-1) AS 数量,
substr(substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')+1),1,8)AS 日期
FROM (
	select id,  substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')) as part
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)
) as t

/*
ID	地区	分类	数量	日期
1	225100	02	3	20160725
1	225100	01	20	20160601
2	225100	03	1	20160701
2	225300	08	80	20160801
2	225400	09	90	20160901
3	225200	02	12	20160501
4		01	3	20160725
5	225200	01	2	20160401
6	225200	03		20160411
7	225300	02	3	20160725
8	225300	02	14	
9	225300	01	3	20160201
10	225300	01	3	20160301
*/
能不能给改动下，使之可以在sqllite下运行呢？
lixizheng 2017-01-02
  • 打赏
  • 举报
 回复
引用 5 楼 ch21st 的回复:
我把你的代码也结合进来，并找了一个由三部分组成的数据，你看看 

insert into chq3g4(id,part)
  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all
  select 2,'225100:03:1:20160701;225300:08:80:20160801;225400:09:90:20160901' union all
  select 3,'225200:02:12:20160501' union all
  select 4,':01:3:20160725' union all
  select 5,'225200:01:2:20160401' union all
  select 6,'225200:03::20160411' union all
  select 7,'225300:02:3:20160725' union all
  select 8,'225300:02:14:' union all
  select 9,'225300:01:3:20160201' union all
  select 10,'225300:01:3:20160301'

WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<100)

SELECT ID,part,
substr(part,1,(INSTR(part,':')-1))AS 地区,
substr(substr(part,INSTR(part,':')+1),1,instr(substr(part,INSTR(part,':')+1),':')-1) AS 分类,
substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),1,instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')-1) AS 数量,
substr(substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')+1),1,8)AS 日期
FROM (
	select id,  substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')) as part
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)
) as t

/*
ID	地区	分类	数量	日期
1	225100	02	3	20160725
1	225100	01	20	20160601
2	225100	03	1	20160701
2	225300	08	80	20160801
2	225400	09	90	20160901
3	225200	02	12	20160501
4		01	3	20160725
5	225200	01	2	20160401
6	225200	03		20160411
7	225300	02	3	20160725
8	225300	02	14	
9	225300	01	3	20160201
10	225300	01	3	20160301
*/
引用 5 楼 ch21st 的回复:
我把你的代码也结合进来，并找了一个由三部分组成的数据，你看看 

insert into chq3g4(id,part)
  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all
  select 2,'225100:03:1:20160701;225300:08:80:20160801;225400:09:90:20160901' union all
  select 3,'225200:02:12:20160501' union all
  select 4,':01:3:20160725' union all
  select 5,'225200:01:2:20160401' union all
  select 6,'225200:03::20160411' union all
  select 7,'225300:02:3:20160725' union all
  select 8,'225300:02:14:' union all
  select 9,'225300:01:3:20160201' union all
  select 10,'225300:01:3:20160301'

WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<100)

SELECT ID,part,
substr(part,1,(INSTR(part,':')-1))AS 地区,
substr(substr(part,INSTR(part,':')+1),1,instr(substr(part,INSTR(part,':')+1),':')-1) AS 分类,
substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),1,instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')-1) AS 数量,
substr(substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')+1),1,8)AS 日期
FROM (
	select id,  substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')) as part
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)
) as t

/*
ID	地区	分类	数量	日期
1	225100	02	3	20160725
1	225100	01	20	20160601
2	225100	03	1	20160701
2	225300	08	80	20160801
2	225400	09	90	20160901
3	225200	02	12	20160501
4		01	3	20160725
5	225200	01	2	20160401
6	225200	03		20160411
7	225300	02	3	20160725
8	225300	02	14	
9	225300	01	3	20160201
10	225300	01	3	20160301
*/
非常感谢，我放在sqlserver里运行是正常的，但这个使用的是sqllite数据库，而且只能使用sqllite这个数据库，您回复的两端代码在sqllite里都运行不了，有些功能sqllite上不支持。
lixizheng 2017-01-01
  • 打赏
  • 举报
 回复
引用 3 楼 ch21st 的回复:
你这不是已经解析出来了吗？ 至于分号分行你可以参考下面的做法 

WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<1000)

	select id, a.part, substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')-1) as part1
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)

/*
225100:02:3:20160725;225100:01:20:20160601	225100:02:3:20160725
225100:02:3:20160725;225100:01:20:20160601	225100:01:20:2016060
225100:03:1:20160701	225100:03:1:20160701
225200:02:12:20160501	225200:02:12:20160501
:01:3:20160725	:01:3:20160725
225200:01:2:20160401	225200:01:2:20160401
225200:03::20160411	225200:03::20160411
225300:02:3:20160725	225300:02:3:20160725
225300:02:14:	225300:02:14:
225300:01:3:20160201	225300:01:3:20160201
225300:01:3:20160301	225300:01:3:20160301
*/
这个别人给解答的，我运行后发现少了id为1的分号后面那条数据，我不知道怎么读取出来，能帮忙在那条SQL语句上改进一下吗？
道素 2017-01-01
  • 打赏
  • 举报
 回复
你这不是已经解析出来了吗？ 至于分号分行你可以参考下面的做法 

WITH RECURSIVE
  cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<1000)

	select id, a.part, substr(a.part,case when cnt.x=0 then 1 else cnt.x+2 end,instr(a.part||';',';')-1) as part1
	from chq3g4 as a 
	left join cnt on (substr(a.part,cnt.x+1,1)=';' or x=0)

/*
225100:02:3:20160725;225100:01:20:20160601	225100:02:3:20160725
225100:02:3:20160725;225100:01:20:20160601	225100:01:20:2016060
225100:03:1:20160701	225100:03:1:20160701
225200:02:12:20160501	225200:02:12:20160501
:01:3:20160725	:01:3:20160725
225200:01:2:20160401	225200:01:2:20160401
225200:03::20160411	225200:03::20160411
225300:02:3:20160725	225300:02:3:20160725
225300:02:14:	225300:02:14:
225300:01:3:20160201	225300:01:3:20160201
225300:01:3:20160301	225300:01:3:20160301
*/
lixizheng 2017-01-01
  • 打赏
  • 举报
 回复 1
引用 1 楼 ch21st 的回复:


;with tb(id,part) AS (

  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all

  select 2,'225100:03:1:20160701' union all

  select 3,'225200:02:12:20160501' union all

  select 4,':01:3:20160725' union all

  select 5,'225200:01:2:20160401' union all

  select 6,'225200:03::20160411' union all

  select 7,'225300:02:3:20160725' union all

  select 8,'225300:02:14:' union all

  select 9,'225300:01:3:20160201' union all

  select 10,'225300:01:3:20160301'



),p AS (

select id

,r.si,DENSE_RANK()OVER(PARTITION BY id ORDER BY CONVERT(VARCHAR,r.si)) AS rn,'data'+LTRIM(ROW_NUMBER()OVER(PARTITION BY id,r.si ORDER BY d.k)) AS title

,d.v 

FROM tb

CROSS APPLY(values(CONVERT(XML,'<r><n>'+replace(replace(tb.part,':','</n><n>'),';','</n></r><r><n>')+'</n></r>'))) x(n)

OUTER APPLY (SELECT s.b.query('.') AS i,s.b.value('.','varchar(500)') si  FROM x.n.nodes('r')s(b)) r

OUTER APPLY (SELECT s.b.value('.','varchar(500)') AS v,ROW_NUMBER()OVER(ORDER BY GETDATE()) k FROM r.i.nodes('r/n')s(b)) d



)

--DROP TABLE #tt

SELECT * INTO #tt FROM p

SELECT * FROM #tt 

DECLARE @col VARCHAR(max),@sql VARCHAR(max)

SELECT @col=ISNULL(@col+',','')+title FROM #tt GROUP BY title ORDER BY title

PRINT @col

SET @sql='

SELECT id,'+@col+' FROM #tt

PIVOT(MAX(v) FOR title IN ('+@col+')) p'

EXEC(@sql)

/*

id	data1	data2	data3	data4

1	225100	01	20	20160601

1	225100	02	3	20160725

2	225100	03	1	20160701

3	225200	02	12	20160501

4		01	3	20160725

5	225200	01	2	20160401

6	225200	03		20160411

7	225300	02	3	20160725

8	225300	02	14	

9	225300	01	3	20160201

10	225300	01	3	20160301

*/


你好，这里使用的是SQLLite数据库，用不了存储过程，能有其它方法吗？目前做到这样了id1-10里的字符串全部转化为4个字段正常读取了，就是id为1的字符串，中间有个分号，等于里面有两条数据，不知道该怎么读取了。这是我目前的sql语句：
SELECT
substr(part,1,(INSTR(part,':')-1))AS 地区,
substr(substr(part,INSTR(part,':')+1),1,instr(substr(part,INSTR(part,':')+1),':')-1) AS 分类,
substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),1,instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')-1) AS 数量,
substr(substr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),instr(substr(substr(part,INSTR(part,':')+1),instr(substr(part,INSTR(part,':')+1),':')+1),':')+1),1,8)AS 日期
FROM chq3g4
-----------------
道素 2017-01-01
  • 打赏
  • 举报
 回复 

;with tb(id,part) AS (
  select 1,'225100:02:3:20160725;225100:01:20:20160601' union all
  select 2,'225100:03:1:20160701' union all
  select 3,'225200:02:12:20160501' union all
  select 4,':01:3:20160725' union all
  select 5,'225200:01:2:20160401' union all
  select 6,'225200:03::20160411' union all
  select 7,'225300:02:3:20160725' union all
  select 8,'225300:02:14:' union all
  select 9,'225300:01:3:20160201' union all
  select 10,'225300:01:3:20160301'

),p AS (
select id
,r.si,DENSE_RANK()OVER(PARTITION BY id ORDER BY CONVERT(VARCHAR,r.si)) AS rn,'data'+LTRIM(ROW_NUMBER()OVER(PARTITION BY id,r.si ORDER BY d.k)) AS title
,d.v 
FROM tb
CROSS APPLY(values(CONVERT(XML,'<r><n>'+replace(replace(tb.part,':','</n><n>'),';','</n></r><r><n>')+'</n></r>'))) x(n)
OUTER APPLY (SELECT s.b.query('.') AS i,s.b.value('.','varchar(500)') si  FROM x.n.nodes('r')s(b)) r
OUTER APPLY (SELECT s.b.value('.','varchar(500)') AS v,ROW_NUMBER()OVER(ORDER BY GETDATE()) k FROM r.i.nodes('r/n')s(b)) d

)
--DROP TABLE #tt
SELECT * INTO #tt FROM p
SELECT * FROM #tt 
DECLARE @col VARCHAR(max),@sql VARCHAR(max)
SELECT @col=ISNULL(@col+',','')+title FROM #tt GROUP BY title ORDER BY title
PRINT @col
SET @sql='
SELECT id,'+@col+' FROM #tt
PIVOT(MAX(v) FOR title IN ('+@col+')) p'
EXEC(@sql)
/*
id	data1	data2	data3	data4
1	225100	01	20	20160601
1	225100	02	3	20160725
2	225100	03	1	20160701
3	225200	02	12	20160501
4		01	3	20160725
5	225200	01	2	20160401
6	225200	03		20160411
7	225300	02	3	20160725
8	225300	02	14	
9	225300	01	3	20160201
10	225300	01	3	20160301
*/
sqlserver中根据某个字符切割字符串函数
sqlserver中根据某个字符切割字符串函数，比如根据逗号切割字符串，结果返回的是一个表值函数，这个函数返回结果字段包含Id和Value，Id为序号，Value为切割后的值
plsql分割字符串
plsql分割字符串
sqlserver 字符串分割、包含、匹配
2、按指定符号分割字符串，返回“分割后指定索引的第几个元素”的值，象数组一样方便 3、检查一个元素是否在数组中，返回1或0 4、检查一个元素是否与数组中的相匹配，并返回相匹配的数组的值。参数值为0表示全模糊...
sql字符串分割
sql字符串分割,sqlserver 表值函数， 对包含逗号的字符串进行分割并返回表。
SQL字符串分割
在查询表时需要对单个字段分割成多个字段非常有用！
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章