-
-
Save acanalesg/43eaf53226d9e23b28330e0a148ed827 to your computer and use it in GitHub Desktop.
sql scaler function to return an item at a specific index in a split string. Was useful to me in a select clause parsing a value like Age_0_14
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE FUNCTION dbo.itemAtIndexInASplitString( @stringToSplit VARCHAR(MAX), @delimiter VARCHAR(5), @indexToReturn int) | |
RETURNS | |
varchar(max) | |
AS | |
BEGIN | |
DECLARE @returnList TABLE ([ID] int, [Name] [nvarchar] (500)) | |
DECLARE @name NVARCHAR(255) | |
DECLARE @pos INT | |
DECLARE @index INT | |
DECLARE @return_value varchar(max) | |
SET @index = 0 | |
SET @return_value = null | |
WHILE CHARINDEX(@delimiter, @stringToSplit) > 0 | |
BEGIN | |
SELECT @pos = CHARINDEX(@delimiter, @stringToSplit) | |
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1) | |
INSERT INTO @returnList | |
SELECT @index, @name | |
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos) | |
SET @index = @index + 1 | |
END | |
INSERT INTO @returnList | |
SELECT @index, @stringToSplit | |
SELECT @return_value = Name from @returnList where [ID] = @indexToReturn | |
RETURN @return_value | |
END | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment