Monday, December 24, 2012

How to Split String in SQL Server database | Splitting comma separted String with Example

Some time we need to split a long comma separated String in Stored procedure  e.g. Sybase or SQL Server stored procedures. Its quite common to pass comma delimited or delimiter separated String as input parameter to Stored procedure and than later split comma separated String into multiple values inside stored proc. This is not just case of input parameter but you can also have comma separated string in any table data. Unfortunately there is no split() function in Sybase or SQL Server 2005 or 2008 which can directly split string based on delimiter just like in Java string split method. Fortunately Sybase Adaptive Server and Microsoft SQL server has functions like CHARINDEX and PATINDEX which can be used to split comma separated String. This is next on our SQL tutorials after seeing SQL query to find duplicate records in table and How to find 2nd and Nth maximum salary in SQL.

By the way both CHARINDEX and PATINDEX allows to specify delimiter, so you are not tied with comma. Apart from this two builtin function and return position of delimiter in String, You need to use Sybase SQL function LEFT() which return substring in Sybase more precisely left of original string form position 1 to specified position. we also need to use function STUFF to update original String and remove the first substring out of it. STUFF allows you to delete characters from String and attach specified characters. Here we are not attaching anything and passed null to simply delete character from position 1 to index of comma. In next section we will see example of splitting String in Sybase and SQL Server using both CHARINDEX and PATINDEX function.


Sybase CHARINDEX Example to Split String

Sybase PATINDEX and CHARINDEX example - split String in SQL ServerHere is code example of How to split string in Sybase adaptive server using CHARINDEX function. This can be used in any stored procedure to split any comma delimited String. In this example we have used CHARINDEX, LEFT and STUFF function to split comma delimited String into multiple values.

declare @string varchar(500)
SET @string = 'abc,xyx,def'
declare @pos numeric(20)
declare @piece varchar(50)

SET @pos = charindex(',' , @string)
while @pos <> 0
begin
SET @piece = LEFT(@string, @pos-1)
print @piece
SET @string = stuff(@string, 1, @pos, NULL)
SET @pos = charindex(',' , @string)
end
print @string  --this is required to print last string

Output:
abc
xyx
def


How to split string in Sybase using PATINDEX

In last section we have seen how to split String in stored procedure on Sybase database using CHARINDEX function but we can also split String using PATINDEX function as shown in below stored procedure snippet. This stored procedure snippet is not much different than previous one,  just two %sign and than character (,) to specify pattern.  Main difference between PATINDEX and CHARINDEX function in Sybase is that PATINDEX supports wildcards in search string which is not supported by CHARINDEX function. Here is sample code to split String using PATINDEX in Sybase or SQL Server database.

declare @string varchar(500)
SET @string = 'abc,xyx,def'
declare @pos numeric(20)
declare @piece varchar(50)

SET @pos = patindex('%,%' , @string)
while @pos <> 0
begin
SET @piece = LEFT(@string, @pos-1)
print @piece
SET @string = stuff(@string, 1, @pos, NULL)
SET @pos = charindex(',' , @string)
end
print @string  --this is required to print last string

Output:
abc
xyx
def


That’s all on How to split String in Stored procedure in Sybase or SQL Server. As I found most of think which works on Sybase also works on SQL Server, this stored procedure snippet will most likely work on both Sybase and SQL Server. You can not only split comma delimited String but also any other delimiter e.g. PIPE (|) or Colon [:] . After splitting String , You can either print the individual string or insert them into table, its your choice.

Other database and SQL tutorials from Javarevisited Blog

No comments :

Post a Comment