各位大侠,帮忙看看
以下表中
id str
1 m=2400&l=240320&f=2424&p=0&pid=COEELAND042&s=D36_LQVG&first=true
2 fileid=649&s=HQX_s808&first=true
3 m=2400&l=240320&f=2424&p=0&pid=COEELAND042&s=D36_LQVG&first=true
4 m=2303&l=240320&f=2424&p=0&pid=COEELAND041&s=B01_M90X&first=true&t=50665
5 fileid=1956&s=HQX_s808&first=true&t=63661
6 m=2303&l=240320&f=2424&p=0&pid=COEELAND023&s=B01_M90X&first=true&t=63828
7 m=2400&l=240320&f=2424&p=0&pid=CHANGXIA004&s=D01_R330&first=true
通过T-sql 怎么把字段str中各参数解析出来例如第一条语句:
m=2400
l=240320
f=2424
p=0
pid=coeelando42
s=d36_lqvg
first=true怎么把结果写出来?然后存入新表中。
以下表中
id str
1 m=2400&l=240320&f=2424&p=0&pid=COEELAND042&s=D36_LQVG&first=true
2 fileid=649&s=HQX_s808&first=true
3 m=2400&l=240320&f=2424&p=0&pid=COEELAND042&s=D36_LQVG&first=true
4 m=2303&l=240320&f=2424&p=0&pid=COEELAND041&s=B01_M90X&first=true&t=50665
5 fileid=1956&s=HQX_s808&first=true&t=63661
6 m=2303&l=240320&f=2424&p=0&pid=COEELAND023&s=B01_M90X&first=true&t=63828
7 m=2400&l=240320&f=2424&p=0&pid=CHANGXIA004&s=D01_R330&first=true
通过T-sql 怎么把字段str中各参数解析出来例如第一条语句:
m=2400
l=240320
f=2424
p=0
pid=coeelando42
s=d36_lqvg
first=true怎么把结果写出来?然后存入新表中。
把str换成
isnert into tb select m=2400 select l=240320 。
然后执行这句话
-- Author :SQL77(只为思齐老)
-- Date :2010-05-11 16:23:11
-- Version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
-- Oct 14 2005 00:33:37
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([id] int,[str] varchar(72))
insert #tb
select 1,'m=2400&l=240320&f=2424&p=0&pid=COEELAND042&s=D36_LQVG&first=true' union all
select 2,'fileid=649&s=HQX_s808&first=true' union all
select 3,'m=2400&l=240320&f=2424&p=0&pid=COEELAND042&s=D36_LQVG&first=true' union all
select 4,'m=2303&l=240320&f=2424&p=0&pid=COEELAND041&s=B01_M90X&first=true&t=50665' union all
select 5,'fileid=1956&s=HQX_s808&first=true&t=63661' union all
select 6,'m=2303&l=240320&f=2424&p=0&pid=COEELAND023&s=B01_M90X&first=true&t=63828' union all
select 7,'m=2400&l=240320&f=2424&p=0&pid=CHANGXIA004&s=D01_R330&first=true'
--------------开始查询--------------------------select
id,
substring([str],number,charindex('&',[str]+'&',number)-number) [str]
from #tb ,
master..spt_values where type='p'
and number <=len([str])
and substring('&'+[str],number,1)='&'
----------------结果----------------------------
/* (7 行受影响)
id str
----------- ------------------------------------------------------------------------
1 m=2400
1 l=240320
1 f=2424
1 p=0
1 pid=COEELAND042
1 s=D36_LQVG
1 first=true
2 fileid=649
2 s=HQX_s808
2 first=true
3 m=2400
3 l=240320
3 f=2424
3 p=0
3 pid=COEELAND042
3 s=D36_LQVG
3 first=true
4 m=2303
4 l=240320
4 f=2424
4 p=0
4 pid=COEELAND041
4 s=B01_M90X
4 first=true
4 t=50665
5 fileid=1956
5 s=HQX_s808
5 first=true
5 t=63661
6 m=2303
6 l=240320
6 f=2424
6 p=0
6 pid=COEELAND023
6 s=B01_M90X
6 first=true
6 t=63828
7 m=2400
7 l=240320
7 f=2424
7 p=0
7 pid=CHANGXIA004
7 s=D01_R330
7 first=true(44 行受影响)
*/