sql server返回参数值,根据url

2021年3月3日 21点热度 0条评论 来源: Better_Roy

目录

提示

此函数速度稍稍有些慢,可以根据实际业务,做缓存等进行优化。

1、语句,直接执行即可

ALTER  FUNCTION [dbo].[GetParamByUrl]
(
	@url VARCHAR(1000),	--地址
	@para varchar(100)	--待获取值的参数名
)
RETURNS VARCHAR(300)
AS
BEGIN
	DECLARE @str VARCHAR(MAX) = '';
	DECLARE @item2 VARCHAR(MAX) = '';
	DECLARE @item VARCHAR(MAX) = '';
		DECLARE @a1 TABLE (id2 INT,Item2 VARCHAR(1000));
		SELECT @str = Item FROM [dbo].[S_Split_String](@url, '?') WHERE ID = 2;
	IF(@str IS NULL OR @str='')
	BEGIN
		SELECT @item=null;
	END;
	ELSE
	BEGIN
		DECLARE @t1 TABLE (id INT,Item VARCHAR(1000));
		INSERT INTO @t1(id,Item)
		SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS id,Item FROM [dbo].[S_Split_String](LOWER(@str), '&')
		WHERE CHARINDEX(@para, Item) > 0;
		--select * from @t1
	 
		INSERT INTO @a1(id2,Item2)
		SELECT ROW_NUMBER() OVER (ORDER BY id) AS id2,SUBSTRING(Item, 1, CHARINDEX('=', Item) - 1) AS Item2 FROM @t1;
		SELECT @item2 = Item FROM @t1 WHERE id = (SELECT id2 FROM @a1 WHERE Item2 = @para);
		SELECT @item=Item FROM [dbo].[S_Split_String](@item2, '=') WHERE ID = 2;
	END;
	RETURN @item

END

2、调用方法

select dbo.[GetParamByUrl]('/buess/quality/Luminoscope/Frm_LSDay01_1.aspx?pagecode=101&sd=-3&sd1=0&materialcode=7010100001','materialcode')
    原文作者:Better_Roy
    原文地址: https://blog.csdn.net/hello_mr_anan/article/details/114319604
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。