If there is a string eg: "Sun,star,moon,clouds", in MS SQL, you want to "break this string down into elements [sun] [star] [Moon] [clouds], according to the given delimiter," and how do you implement it? To do this, create a function with the following code:
Copy Code code as follows:
CREATE FUNCTION [dbo]. [Split_strbydelimiter] (@String VARCHAR (8000), @Delimiter CHAR (1))
RETURNS @temptable TABLE (Items VARCHAR (8000))
As
BEGIN
DECLARE @idx INT
DECLARE @slice VARCHAR (8000)
SELECT @idx = 1
IF Len (@String) <1 OR @String is NULL return
While @idx!= 0
BEGIN
SET @idx = charindex (@Delimiter, @String)
IF @idx!=0
SET @slice = Left (@String, @idx-1)
ELSE
SET @slice = @String
IF (Len (@slice) >0)
INSERT into @temptable (Items) VALUES (@slice)
SET @String = Right (@String, Len (@String)-@idx)
IF len (@String) = 0 break
End
Return
End
Example: If you enter
SELECT * FROM dbo. Split_strbydelimiter (' Sun,star,moon,clouds ', ', ', ')
Results return
Sun
Star
Moon
Clouds
In the above code to do the transformation, return how many elements
Copy Code code as follows:
CREATE FUNCTION [dbo]. [Getcount_split_strbydelimiter] (@String VARCHAR (8000), @Delimiter CHAR (1))
RETURNS INT
As
BEGIN
DECLARE @temptable TABLE (Items VARCHAR (8000))
DECLARE @SplitCount INT
DECLARE @idx INT
DECLARE @slice VARCHAR (8000)
SELECT @idx = 1
IF Len (@String) <1 OR @String is NULL return 0
While @idx!= 0
BEGIN
SET @idx = charindex (@Delimiter, @String)
IF @idx!=0
SET @slice = Left (@String, @idx-1)
ELSE
SET @slice = @String
IF (Len (@slice) >0)
INSERT into @temptable (Items) VALUES (@slice)
SET @String = Right (@String, Len (@String)-@idx)
IF len (@String) = 0 break
End
SET @SplitCount = (SELECT COUNT (*) from @temptable)
Return @SplitCount
End
Example
SELECT dbo. Getcount_split_strbydelimiter (' Sun,star,moon,clouds ', ', ', ')
Results return
4
