Sybase and SQL Server PATINDEX CHARINDEX Example to split String in Stored procedure

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 2^nd 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

Here 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

Difference between correlated and noncorrelated subquery in SQL

When to use truncate vs delete in SQL

10 SELECT command examples in SQL

How to create table by copying schema from another table in SQL

How to join three tables in single SQL query

Please share with your friends if like this article