27,580
社区成员
发帖
与我相关
我的任务
分享
数据库字段中有如下数据:http://item.taobao.com/item.htm?id=7835939979&ProdID=123123&pro=321312
declare @num int , @count int,@oth int, @str nvarchar(200)
select @str='http://item.taobao.com/item.htm?id=7835939979&ProdID=123123&pro=321312'
select @count=len(@str)
select @num=PATINDEX ('%[&]%',@str)
select @oth=PATINDEX('%[&]%', REVERSE(@str))
select SUBSTRING(@str,@num+1,@count-@oth-@num)
declare @num int , @count int,@oth int, @str nvarchar(200)
select @str='http://item.taobao.com/item.htm?id=7835939979&ProdID=123123&pro=321312'
select @count=len(@str)
select @num=PATINDEX ('%[&]%',@str)
select @oth=PATINDEX('%[&]%', REVERSE(@str))
select SUBSTRING(@str,@num+1,@count-@oth-@num)
declare @num int , @count int,@str nvarchar(200)
select @str='http://item.taobao.com/item.htm?id=7835939979&ProdID=123123&pro=321312'
select @count=len('http://item.taobao.com/item.htm?id=7835939979&ProdID=123123&pro=321312')
select @num=PATINDEX ('%[?]%','http://item.taobao.com/item.htm?id=7835939979&ProdID=123123&pro=321312')
select parsename(replace(right(@str,@count-@num),'&','.'),2)
DECLARE @s1 varchar(200)
DECLARE @s2 varchar(200)
set @s1='http://item.taobao.com/item.htm?id=7835939979&ProdID=123123&pro=321312'
set @s2='http://item.taobao.com/item.htm?id=7835939979&ProdID=123123'
select substring(
@s1,
charindex('ProdID=',@s1,1),
case when charindex('&pro=',@s1,1)=0 then len(@s1)+1
else charindex('&pro=',@s1,1) end - charindex('ProdID=',@s1,1))
select substring(
@s2,
charindex('ProdID=',@s2,1),
case when charindex('&pro=',@s2,1)=0 then len(@s2)+1
else charindex('&pro=',@s2,1) end - charindex('ProdID=',@s2,1))
结果
ProdID=123123
ProdID=123123
DECLARE @s varchar(200)
set @s='http://item.taobao.com/item.htm?id=7835939979&ProdID=123123&pro=321312'
select substring(
@s,
charindex('ProdID=',@s,1),
charindex('&pro=',@s,1)-charindex('ProdID=',@s,1))
结果
ProdID=123123
declare @sql varchar(100)
set @sql='http://item.taobao.com/item.htm?id=7835939979&ProdID=123123&pro=321312'
select substring(@sql,charindex('ProdID',@sql),13)
/*
ProdID=123123
*/
declare @s varchar(100)
set @s='http://item.taobao.com/item.htm?id=7835939979&ProdID=123123&pro=321312'
select
left(
stuff(@s,1,charindex('prodid',@s)-1,''),
charindex('&',stuff(@s,1,charindex('prodid',@s),''))-1
)
/**
---------------------------------
ProdID=12312
(1 行受影响)
**/
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id varchar(70))
insert into #tb
select 'http://item.taobao.com/item.htm?id=7835939979&ProdID=123123&pro=321312'
select substring(id,charindex('ProdID=',id),charindex('&pro=',id)-charindex('ProdID=',id))
from #tb
----------------------------------------------------------------------
ProdID=123123
(1 行受影响)
select
left(
stuff(col,1,charindec('prodid',col),''),
charindex('&',stuff(col,1,charindec('prodid',col),''))-1
)