In this article we are going to see a split function in SQL SERVER. How the split function works. The first parameter for the function takes the word and second parameter takes the group of condition based on words has to be split “.,@#$ %^” etc.
For
Example:
Rajesh,…Is a,C##Developer
Output:
Rajesh
Is
A
C#
Developer
Let we see the function Now,
CREATE FUNCTION [DBO].[SPLIT]
(
@DATA
NVARCHAR(MAX),
@SPLITCONDITION
NVARCHAR(30)
)
RETURNS @VALUE TABLE(TEXT NVARCHAR(MAX))
AS
BEGIN
DECLARE @CONLEN INT = LEN (@SPLITCONDITION)
IF @CONLEN
< 1
BEGIN
INSERT
INTO @VALUE(TEXT) SELECT @DATA
RETURN
END
ELSE
BEGIN
DECLARE
@LEN INT
= LEN(@DATA)
DECLARE
@LOOP INT = 0
DECLARE
@TEMPDATA NVARCHAR(MAX)
DECLARE
@CHAR CHAR(1)
SELECT
@TEMPDATA = ''
WHILE(@LOOP <= @LEN)
BEGIN
SELECT
@CHAR = SUBSTRING(@DATA,@LOOP,1)
IF
CHARINDEX(@CHAR,@SPLITCONDITION) > 0
BEGIN
IF @TEMPDATA <>
''
BEGIN
INSERT
INTO @VALUE(TEXT) SELECT @TEMPDATA
SELECT
@TEMPDATA = ''
END
END
ELSE
BEGIN
SELECT
@TEMPDATA = @TEMPDATA +
@CHAR
END
SELECT
@LOOP = @LOOP+1
END
IF
@TEMPDATA <>''
INSERT
INTO @VALUE(TEXT) SELECT @TEMPDATA
RETURN
END
RETURN
END
How to call the Split function ?
SELECT TEXT FROM DBO.SPLIT('C#@IS,A,.OBJECT ORIENTED$LANGUAGE.,','.,$@ &')
Output :
|
|
TEXT
|
|
1
|
C#
|
|
2
|
IS
|
|
3
|
A
|
|
4
|
OBJECT
|
|
5
|
ORIENTED
|
|
6
|
LANGUAGE
|
From this article we can see how the user defined split function is created in SQL server and executed.
