I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value.
Ex: "one,two,three,four" would return a new table with only one column containing the following values:
one two three four
Does this code look error prone to you guys? When I test it with
SELECT * FROM utvf_Split('one,two,three,four',',')
it just runs forever and never returns anything. This is getting really disheartening especially since there are no built in split functions on MSSQL server (WHY WHY WHY?!) and all the similar functions I've found on the web are absolute trash or simply irrelevant to what I'm trying to do.
Here is the function:
USE *myDBname* GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[utvf_SPlit] (@String VARCHAR(MAX), @delimiter CHAR) RETURNS @SplitValues TABLE ( Asset_ID VARCHAR(MAX) NOT NULL ) AS BEGIN DECLARE @FoundIndex INT DECLARE @ReturnValue VARCHAR(MAX) SET @FoundIndex = CHARINDEX(@delimiter, @String) WHILE (@FoundIndex <> 0) BEGIN DECLARE @NextFoundIndex INT SET @NextFoundIndex = CHARINDEX(@delimiter, @String, @FoundIndex+1) SET @ReturnValue = SUBSTRING(@String, @FoundIndex,@NextFoundIndex-@FoundIndex) SET @FoundIndex = CHARINDEX(@delimiter, @String) INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue) END RETURN END