在sqlserver中创建表值函数

桃扇骨 2021-09-24 04:50 566阅读 0赞

1、创建表值函数SplitStr（用逗号分割字符串）

ALTER FUNCTION [dbo].[SplitStr] 
    (@RowData nvarchar(4000), @SplitOn nvarchar(4000)) 
    RETURNS @RtnValue TABLE (DATA nvarchar(4000)) 
    AS 
    BEGIN 
    	DECLARE @Cnt int
    	SET @Cnt = 1 
    	While (Charindex(@SplitOn,@RowData)>0)
    		BEGIN
    			INSERT INTO @RtnValue (DATA)
    			SELECT DATA = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
    			SET @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    			SET @Cnt = @Cnt + 1
    		END
    	INSERT INTO @RtnValue (DATA)
    	SELECT DATA = ltrim(rtrim(@RowData)) 
    	RETURN 
    END

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xfZGVnZWdl_size_16_color_FFFFFF_t_70][]

注：[https://www.cnblogs.com/OpenCoder/p/10658742.html][https_www.cnblogs.com_OpenCoder_p_10658742.html]

[https://www.cnblogs.com/csdbfans/p/3514538.html][https_www.cnblogs.com_csdbfans_p_3514538.html]

2、数据库中有的字段的值为多个值以逗号连接而成

![2020081416222925.png][]

![20200814173619323.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xfZGVnZWdl_size_16_color_FFFFFF_t_70 1][]

注：根据逗号将字符串分隔

[https://blog.csdn.net/sqlserverdiscovery/article/details/79187307][https_blog.csdn.net_sqlserverdiscovery_article_details_79187307]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xfZGVnZWdl_size_16_color_FFFFFF_t_70]: /images/20210923/495bef30e8814f6f98b74e557029b034.png
[https_www.cnblogs.com_OpenCoder_p_10658742.html]: https://www.cnblogs.com/OpenCoder/p/10658742.html
[https_www.cnblogs.com_csdbfans_p_3514538.html]: https://www.cnblogs.com/csdbfans/p/3514538.html
[2020081416222925.png]: /images/20210923/22e603afeee04e3596a3bb1b74ccbb01.png
[20200814173619323.png]: /images/20210923/789ebdb5547a402085a2f5ad5438985e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xfZGVnZWdl_size_16_color_FFFFFF_t_70 1]: /images/20210923/bdfd93d970f04cc19cb4ba119fa760ab.png
[https_blog.csdn.net_sqlserverdiscovery_article_details_79187307]: https://blog.csdn.net/sqlserverdiscovery/article/details/79187307