This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a function to split a string by using a different type of delimiters in SQL Server.
I created a function because splitting a string is a very common requirement for all SQL Server Database Developer and created in such a way that you guys can pass any delimiter and split your string.
This is a function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE FUNCTION [dbo].[SplitString_ByAnyDelimiter] ( @String NVARCHAR(MAX), @Delimiter NVARCHAR(100) ) RETURNS @MyString TABLE (Result NVARCHAR(MAX)) AS BEGIN DECLARE @xml XML SET @xml = N' INSERT INTO @MyString(Result) SELECT r.value('.','varchar(MAX)') AS Value FROM @xml.nodes('/t') AS records(r) RETURN END |
Sample Execution:
|
1 2 3 4 5 6 7 8 |
SELECT * FROM dbo.[SplitString_ByAnyDelimiter]('My.Name.is.Anvesh','.') GO SELECT * FROM dbo.[SplitString_ByAnyDelimiter]('My|Name|is|Anvesh','|') GO SELECT * FROM dbo.[SplitString_ByAnyDelimiter]('My,Name,is,Anvesh',',') GO SELECT * FROM dbo.[SplitString_ByAnyDelimiter]('My Name is Anvesh',' ') GO |
The Result:
