Sqlserver 多选代码值转文字函数

星辰

2020-12-28

1265

人

人评论

人举报

[TOC]
# Sqlserver 多选代码值转文字函数

## 多选代码值转文字
```
CREATE FUNCTION [dbo].[F_DMNR_LIST](@str varchar(500),@split varchar(10),@DMJBH varchar(200))  
RETURNS varchar(8000)  
AS  
BEGIN  
	DECLARE @resultstr varchar(8000)
    DECLARE @temstr varchar(200)  
	DECLARE @restr varchar(200)  
	DECLARE @location int
	DECLARE @start int
	
	set @str=ltrim(rtrim(@str))
    set @location=charindex(@split,@str)
	IF @location = 0
		begin
			SET @resultstr = (SELECT DMNR FROM T_COMN_GGDMZ WHERE DMJBH=@DMJBH AND DM=@str)
		end
	else
	begin
		SET @temstr = substring(@str,0,@location) 
		SET @resultstr = (SELECT DMNR FROM T_COMN_GGDMZ WHERE DMJBH=@DMJBH AND DM=@temstr)
	end
    while @location<>0
	  begin
		set @start=@location+1
		set @location=charindex(@split,@str,@start)
		if @location=0
		begin
			SET @temstr = substring(@str,@start,len(@str)-@start+1) 
			SET @restr = (SELECT DMNR  FROM T_COMN_GGDMZ WHERE DMJBH=@DMJBH AND DM=@temstr)
			SET @resultstr = @resultstr+@split+@restr
		end
		else 
		begin
			SET @temstr = substring(@str,@start,@location-@start) 
			SET @restr = (SELECT DMNR  FROM T_COMN_GGDMZ WHERE DMJBH=@DMJBH AND DM=@temstr)
			SET @resultstr = @resultstr+@split+@restr
		end
		
	  end 
	
    RETURN @resultstr  
END
```

## 多选框  包含其中一个查询函数
```
CREATE FUNCTION [F_CONTAINS_STR] ( @str VARCHAR ( 1000 ), --字符串
	@findstr VARCHAR ( 1000 ), --查找字符串
	@split VARCHAR ( 100 ) --分隔符号
	) RETURNS INT AS BEGIN
	DECLARE
		@result INT;
	DECLARE
		@temp VARCHAR ( 100 );--定义变量用于接收单个结果
	
	SET @result = 0;
	IF
		RIGHT ( @findstr, 1 ) <> @split BEGIN
			
			SET @findstr = @findstr + @split;
		
		END WHILE ( @findstr <> '' ) BEGIN
		
		SET @temp = LEFT ( @findstr, CHARINDEX( @split, @findstr, 1 ) - 1 );
		IF
			CHARINDEX( @split + @temp + @split,@split + @str + @split, 1 ) > 0 BEGIN
				RETURN 1 
			END ELSE BEGIN
				
				SET @findstr = STUFF( @findstr, 1, CHARINDEX( @split, @findstr, 1 ), '' );
			
			END 
			END RETURN @result 
END
```

所有评论列表