The split string is one required functionality
implementation while fledging your application. The user-defined function is
useful if you are using a SQL Server machine older than 2016. The main use of
the function is to convert a single string of row data into split table values
in the form of rows. The string can be delimited either by a comma or any
special character in accordance with your data.
Further, if you are using SQL Server version2016 and above, please use the STRING_SPLIT function for better performance.
Additionally, if you face any difficulties in using the STRING_SPLIT function
that possess error as – 'Invalid Object Name STRING_SPLIT', please refer the
details on the link for the fix.
Mostly, we have strings separated by commas
in our majority usages. However, we will see the examples of user-defined functions
accepting just comma delimiters as well as the ones accepting special characters
between the string values.
Split String Function (Accepting the only comma
delimiters)Step 1: Create a function as below:
CREATE FUNCTION
Fn_split_string_OnlyComma
(
@in_string VARCHAR(MAX),
@delimiter VARCHAR(1)
)
RETURNS
@list TABLE(tab_splitstring VARCHAR(100))
AS
BEGIN
WHILE LEN(@in_string) > 0
BEGIN
INSERT INTO @list(tab_splitstring)
SELECT left(@in_string, charindex(@delimiter,
@in_string+',') -1) as
tab_splitstring
SET
@in_string = stuff(@in_string, 1, charindex(@delimiter,
@in_string + @delimiter), '')
end
RETURN
END
Step 2: Select from the function – Fn_split_string_OnlyComma
select * from
Fn_split_string_OnlyComma ('mango,orange,pineapple',',')
Note: If you are using any special
character apart from comma character is going to error out.
Split String Function (Accepting any
special character delimiters)
Step 1: Create a function as below:
CREATE FUNCTION
[dbo].[fn_Split_String_AnySCharacter]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS
@output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE
@start INT, @end INT
SELECT
@start = 1, @end = CHARINDEX(@delimiter,
@string)
WHILE
@start < LEN(@string) + 1 BEGIN
IF @end
= 0
SET @end
= LEN(@string) + 1
INSERT INTO
@output (splitdata)
VALUES(SUBSTRING(@string,
@start, @end - @start))
SET
@start = @end + 1
SET @end
= CHARINDEX(@delimiter, @string,
@start)
END
RETURN
END
Step 2: Select from the function - fn_Split_String_AnySCharacter
Examples of splitting the string with ‘#’
special character:
select * from
[fn_Split_String_AnySCharacter] ('mango#orange#pineapple','#')
Examples of splitting the string with ‘?’
special character:
select * from
[fn_Split_String_AnySCharacter] ('mango?orange?pineapple','?')
Finally, an example of splitting the string
with ‘,’ special character:
select * from
[fn_Split_String_AnySCharacter] ('mango,orange,pineapple',',')
Example of using multiple special characters
in the string:
While passing more than one special character
in the string, the user-defined function we created is going to accept just the special character we mentioned in the @delimiter parameter.
select * from
[fn_Split_String_AnySCharacter] ('mango,orange%pineapple',',')
You'll also like:
"CHOOSE Function"
"Difference Between Clustered And Non-Clustered Index In SQL Server"
"Advantages Of UNION ALL Over Union"
