表结构如下
CREATE TABLE AA
(
ObjId int,
url varchar(200)
)记录如: ObjId url
1 xxxxx?Id=123&statu=C 2 xxxxx?Id =1789&statu=R&type=1 3 xxxxx?Id =423&statu=c&type=2现在有个需求只想取出url中Id的值 如下:1 123
3 1789
3 423
请帮帮忙,谢谢!
CREATE TABLE AA
(
ObjId int,
url varchar(200)
)记录如: ObjId url
1 xxxxx?Id=123&statu=C 2 xxxxx?Id =1789&statu=R&type=1 3 xxxxx?Id =423&statu=c&type=2现在有个需求只想取出url中Id的值 如下:1 123
3 1789
3 423
请帮帮忙,谢谢!
objid,
substring(url,charindex('id=',url)+3,charindex('&statu',url)-1) as id
from AA
declare @aa table (ObjId int,url varchar(50))
insert into @aa
select 1,'xxxxx?Id=123&statu=C' union all
select 2,'xxxxx?Id=1789&statu=R&type=1' union all
select 3,'xxxxx?Id=423&statu=c&type=2'
select objid,id=substring(url,charindex('?',url)+4,charindex('&',url)-4-charindex('?',url)) from @aa
(
ObjId int,
url varchar(200)
)
insert AA select 1 , 'xxxxx?Id=123&statu=C' insert AA select 2 , 'xxxxx?Id =1789&statu=R&type=1' insert AA select 3 , 'xxxxx?Id =423&statu=c&type=2'select objid,urlno=substring(url,charindex('=',url)+1,charindex('&',url)-charindex('=',url)-1) from AA
drop table AA objid urlno
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 123
2 1789
3 423(3 行受影响)
修正一下:---测试数据---
if object_id('[AA]') is not null drop table [AA]
go
create table [AA]([ObjId] int,[url] varchar(28))
insert [AA]
select 1,'xxxxx?Id=123&statu=C' union all
select 2,'xxxxx?Id=1789&statu=R&type=1' union all
select 3,'xxxxx?Id=423&statu=c&type=2'
---查询---
select
objid,
substring(url,charindex('id=',url)+3,charindex('&statu',url)-(charindex('id=',url)+3)) as id
from AA---结果---
objid id
----------- ----------------------------
1 123
2 1789
3 423(所影响的行数为 3 行)
CREATE TABLE tb
( ObjId int,
url varchar(200)
) insert into tb
select 1,'xxxxx?Id=123&statu=C ' union all
select 2,'xxxxx?Id =1789&statu=R&type=1' union all
select 3,'xxxxx?Id =423&statu=c&type=2 ' select * from tb select substring (url,charindex('=',url)+1,(charindex('&',url)-charindex('=',url)-1)) from tbdrop table tb