I cannot wrap my head around on the select UDF statement referred from the link below:
How to split a comma-separated value to columns
CREATE FUNCTION dbo.Split ( @InputString VARCHAR(500), @Delimiter VARCHAR(1) ) RETURNS @Items TABLE ( Item VARCHAR(500) ) AS BEGIN IF @Delimiter = ' ' BEGIN SET @Delimiter = ',' SET @InputString = REPLACE(@InputString, ' ', @Delimiter) END IF (@Delimiter IS NULL OR @Delimiter = '') SET @Delimiter = ',' --INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic --INSERT INTO @Items VALUES (@InputString) -- Diagnostic DECLARE @Item VARCHAR(500) DECLARE @ItemList VARCHAR(500) 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 (@InputString) RETURN END -- End Function GO I checked the permission and also made sure that the function has been created in the correct schema using:
SELECT * FROM INFORMATION_SCHEMA.ROUTINES However, I got into problem when I ran the following:
select dbo.Split(TargetFolderId, ',') from ReportConfig where ReportId = 9 Error:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Split", or the name is ambiguous.
but I can do
select * from dbo.Split('stringA,stringB', ',') Could it be the difference between the SSMS version that both of us are using?
I am using SQL2017 - SSMS v17.4.
