A common requirement is to convert rows from CSV values.If we have string such as ‘ORANGES,APPLES,BANANAS’ then we can create a result set from these
values.
| ORANGES |
| APPLES |
| BANANAS |
Here we will see how to do it using a custom SQL Server function.But before that it will be helpful if we understand some of the useful SQL Server functions which we will be using in our function.
CHARINDEX is a function which searches for the occurrence of a string inside another string and returns the index of the search string if found or 0 if the substring is not contained in the main string.
CHARINDEX ( MainExpression ,SearchExpression , indexToStartSearch)
Following SQL will search for the occurrence of string “sample” in the main string and will return its position.The following will return the value 16.
DECLARE @searchString varchar(50)
SET @searchString='This is just a sample string value'
PRINT CHARINDEX('sample', @searchString, 0)
CHARINDEX will return 0 if the searched string is not found.The below SQL will return 0 as the value ‘info’ is not contained in the main string
DECLARE @searchString varchar(50)
SET @searchString='This is just a sample string value'
PRINT CHARINDEX('info', @searchString, 0)
SUBSTRING is a function which returns a portion of a string.The start location and the length of the substring to return is specified as the function arguments.
SUBSTRING ( expression ,startIndex , lengthOfTheSubstring )
startIndex is the index in the expression from which substring is retreived
lengthOfTheSubstring specifies the length of the substring.
For example the below function will return the value “STRING” since we specified the index as 4 and length as 6.
SELECT SUBSTRING('SQLSTRING', 4, 6)
STUFF function is used to delete a substring from a string and then to insert a new string in its place.
STUFF( expression, startIndex, length, newString )
startIndex is the index in the expression from where to remove the substring
length is the number of characters to delete
newString is the string to replace the deleted string.
The following will return the value ‘SQL SAMPLE STRING’
SELECT STUFF( 'SQLSTRING', 1, 3, 'SQL SAMPLE ' )
Function to Convert Comma Separated Values to Rows
We have implemented a custom using the above functions which will convert the comma separated values to rows.It accepts a parameter of type string and return the rows.
CREATE FUNCTION dbo.SplitCSVs (@CSVString varchar(1000))
RETURNS @SeparatedValues TABLE (Value VARCHAR(100))
AS
BEGIN
DECLARE @CommaPosition INT
WHILE (CHARINDEX(',', @CSVString, 0) > 0)
BEGIN
SET @CommaPosition = CHARINDEX(',', @CSVString, 0)
INSERT INTO @SeparatedValues (Value)
SELECT SUBSTRING(@CSVString, 0, @CommaPosition)
SET @CSVString = STUFF(@CSVString, 1, @CommaPosition, '')
END
INSERT INTO @SeparatedValues (Value)
SELECT @CSVString
RETURN
END
Now we can just pass the comma separated string to our function and it will return the result set based on the string values.
If we execute the below SQL
select * FROM [dbo].[SplitCSVs]('SAMPLE VALUE1,SAMPLE VALUE2,SAMPLE VALUE3')
then we will get the following result set
Leave a Reply