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 Microsoft SQL Server using both CHARINDEX and PATINDEX function.
Sybase CHARINDEX Example to Split String
Here is code example of How tosplit 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 SQL Server 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
Source:http://javarevisited.blogspot.com/2012/12/charindex-padindex-sybase-sqlserver-eample-split-string-stored-procedure.html
Tidak ada komentar:
Posting Komentar