I have varchar values like below
72,73,74
I try to split as a comma after that i want to convert to int above values.Than i want to match Id with my User Table.
CREATE FUNCTION Fn_MyFunction(@MyUserIdValues VARCHAR(100))
RETURNS VARCHAR(300) AS
BEGIN
DECLARE @Result VARCHAR(300) = ''
Select UserName From UserTable
Where MyUserIdValues=UserIdValues
RETURN @Result
@Result must be like below in one column
Joe,Michael,Ricky
Any help will be appreciated.
Thanks.
GROUP_CONCAT and FIND_IN_SET might be a handy for you.
Try this:
SELECT GROUP_CONCAT(UserName)
FROM UserTable
WHERE FIND_IN_SET(MyUserIdValues,'72,73,74');
The classic way of doing this...
/*
create table Users
(
id int,
name nvarchar(max)
)
insert into Users
values
(72, 'Joe'),
(73, 'Michael'),
(74, 'Ricky'),
(75, 'Manny'),
(76, 'Bob')
*/
CREATE FUNCTION dbo.Fn_MyFunction(@IdValues VARCHAR(100))
RETURNS NVARCHAR(max) AS
BEGIN
DECLARE @Result NVARCHAR(max);
DECLARE @delimiter as nchar = ',';
WHILE LEN(@IdValues) <> 0
BEGIN
Declare @CurrentId int;
If CHARINDEX(@delimiter, @IdValues) = 0
begin
Set @CurrentId = cast(@IdValues as int);
Set @IdValues = ''
End
Else
begin
Set @CurrentId = cast(left(@IdValues, charindex(@delimiter, @IdValues) -1) as int)
Set @IdValues = Substring(@IdValues, charindex(@delimiter, @IdValues) +1, len(@IdValues))
End
select @Result = Isnull(@Result + ',', '') + Isnull((Select Name From Users Where Id=@CurrentId),'(unknown)')
END
RETURN @Result
END
GO
Select dbo.Fn_MyFunction('72,73,74')
--Joe,Michael,Ricky
Select dbo.Fn_MyFunction('72,0,74')
--Joe,(unknown),Ricky
Select dbo.Fn_MyFunction('72,73,72,74,74')
--Joe,Michael,Joe,Ricky,Ricky
I had found solution for splitting string and inserting it in a table
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 10/03/2013 11:45:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split] (
@InputString VARCHAR(8000),
@Delimiter VARCHAR(50)
)
RETURNS @Items TABLE (
Item int
)
AS
BEGIN
IF @Delimiter = ' '
BEGIN
SET @Delimiter = ','
SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
END
IF (@Delimiter IS NULL OR @Delimiter = '')
SET @Delimiter = ','
DECLARE @Item VARCHAR(8000)
DECLARE @ItemList VARCHAR(8000)
DECLARE @DelimIndex INT
SET @ItemList = @InputString
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
WHILE (@DelimIndex != 0)
BEGIN
SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
INSERT INTO @Items VALUES (@Item)
-- Set @ItemList = @ItemList minus one less item
SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
END -- End WHILE
IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
BEGIN
SET @Item = @ItemList
INSERT INTO @Items VALUES (@Item)
END
-- No delimiters were encountered in @InputString, so just return @InputString
ELSE INSERT INTO @Items VALUES (cast(@InputString as int))
RETURN
END -- End Function
Select UserName From UserTable where MyUserIdValues in(Select Item from Split('72,73,74',','))
//Call function Split using last query
Check this out, it will solve your problem and it simple and perfect method
Select Username from UserTable where MyUserIdValues IN ' + '('+ @Id +')'
My attempt (i think it's a little neater):
create function dbo.fn_UserNames(@ids varchar(max))
returns varchar(max) as
begin
set @ids = @ids + ','
declare @id table (Id int)
while(@ids != '') begin
insert into @id
select i
from (select substring(@ids, 1, charindex(',', @ids, 0) - 1) i) a
where i != ''
if @ids like '%,%'
set @ids = substring(@ids, charindex(',', @ids, 0) + 1,
len(@ids) - charindex(',', @ids, 0))
else
set @ids = ''
end
declare @ret varchar(max)
select @ret = isnull(@ret, '') + a.UserName + ','
from adhoc.UserTable a
join @id b on a.UserId = b.Id
return @ret
end
Hi you can try this one also.
CREATE FUNCTION [DBO].[FN_SPLIT] ( @STRSTRING VARCHAR(MAX))
RETURNS @NAME TABLE (NAME VARCHAR(MAX))
AS
BEGIN
DECLARE @USERID TABLE(ID INT)
DECLARE @USERS TABLE (ID INT , NAME VARCHAR(50))
INSERT INTO @USERS VALUES (72,'A'),(73,'B'),(74,'C')
;WITH STR_CTE(_START, _STOP) AS
(
SELECT 1, CHARINDEX(',' , @STRSTRING )
UNION ALL
SELECT CAST(_STOP + 1 AS INT), CHARINDEX(',' ,@STRSTRING , CAST((_STOP + 1) AS INT))
FROM
STR_CTE
WHERE _STOP > 0
)
INSERT INTO @USERID (ID)
SELECT
SUBSTRING(@STRSTRING , _START, CASE WHEN _STOP > 0 THEN _STOP -_START ELSE 4000 END) AS ID
FROM STR_CTE
DECLARE @STRNAME VARCHAR(MAX)
SELECT
@STRNAME = COALESCE(@STRNAME+',','') + U.NAME
FROM
@USERID UD
INNER JOIN
@USERS U ON UD.ID = U.ID
INSERT INTO @NAME
SELECT @STRNAME
RETURN;
END
