How to split a string in SQL Server?
In this article i will explain how to Split string in SQL Server (or) how to create Split() function in Sql Server.
Execute the following function:
CREATE FUNCTION [dbo].[Split]
(
@RowData nvarchar(MAX),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
Example 1:
select * from dbo.Split('1,2,3,4,5,6', ',')
Output:
Example 2:
select * from dbo.Split('AAA$BBB$CCC$DDD$EEE$FFF', '$')
Output:
-
CreatedJul 23, 2013
-
UpdatedOct 03, 2020
-
Views2,854
Related Articles
Different types of variables in SQL Server
What is Cross Join in SQL Server
How delete record from parent table when there is Foreign Key relation in SQL Server
What is the differences between CHAR, NCHAR, VARCHAR and NVARCHAR in SQL Server?
How to add or remove a column from a table in SQL Server?
How to insert values into Identity Column in SQL Server?
How to get a fixed-length value in SQL Server?
What is Left Join (or) Left Outer Join in SQL Server?
How to get current week dates in SQL Server?
What are the different types of Date formats in SQL Server?