How to Split String in SQL Server and Sybase? Example Tutorial

Sometimes we need to split a long comma-separated String in a Stored procedure e.g. Sybase or SQL Server stored procedures. It's quite common to pass comma delimited or delimiter separated String as an input parameter to Stored procedure and then later split comma separated String into multiple values inside stored proc. This is not just the case of the input parameter but you can also have a 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 a string based on delimiter just like in the Java string split method. 

Fortunately, Sybase Adaptive Server and Microsoft SQL server have functions like CHARINDEX and PATINDEX which can be used to split comma-separated String. This is also next on our SQL tutorials after seeing SQL query to find duplicate records in a table and How to find 2^nd and Nth maximum salary in SQL.

By the way, both CHARINDEX() and PATINDEX() allow to specify delimiter, so you are not tied with a comma. Apart from these two built-in functions and returning the position of delimiter in String, You need to use Sybase SQL function LEFT() which returns substring in Sybase more precisely left of the original string from position 1 to specified position. 

We also need to use function STUFF to update the 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 a character from position 1 to index of a comma. In the next section, we will see an example of splitting String in Sybase and Microsoft SQL Server using both CHARINDEX and PATINDEX functions.

But, if you are new to the SQL world, it's better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.

Sybase CHARINDEX Example to Split String

Here is a 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 functions 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 SQL Server using PATINDEX? Example

In the last section, we have seen how to split String in a stored procedure on Sybase database using CHARINDEX function but we can also split String using PATINDEX function as shown in the below-stored procedure snippet. This stored procedure snippet is not much different than the previous one, with just a two %sign and then the character (,) to specify the pattern.  

The main difference between the PATINDEX and CHARINDEX function in Sybase is that PATINDEX supports wildcards in search string which is not supported by the CHARINDEX function. 

Here is a 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 the 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 the table, it's 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
• 50 SQL Server Phone Interview Questions with Answers
• 10 SELECT command examples in SQL
• How to create the table by copying schema from another table in SQL
• How to join three tables in a single SQL query
• 5 Courses to learn SQL and Database for Beginners
• 5 Websites to learn SQL from scratch
• Top 50 Database and SQL Interview Questions with Answers

Thanks for reading this SQL Server article so far. If you like this SQL Server String Split example then please share it with your friends and colleagues on Facebook and Twitter. I would appreciate the sharing and it would help us to create more articles like this.