Some fourth dimension nosotros ask to separate a long comma separated String inward Stored physical care for e.g. Sybase or SQL Server stored procedures. Its quite mutual to move past times comma delimited or delimiter separated String every bit input parameter to Stored physical care for together with than afterwards separate comma separated String into multiple values within stored proc. This is non just instance of input parameter but y'all tin flame likewise convey comma separated string inward whatever tabular array data. Unfortunately at that spot is no split() part inward Sybase or SQL Server 2005 or 2008 which tin flame straight separate string based on delimiter just similar inward Java string separate method. Fortunately Sybase Adaptive Server together with Microsoft SQL server has functions similar CHARINDEX together with PATINDEX which tin flame live on used to separate comma separated String. This is side past times side on our SQL tutorials after seeing SQL enquiry to discovery duplicate records inward table together with How to discovery 2nd together with Nth maximum salary inward SQL.
By the agency both CHARINDEX() together with PATINDEX() allows to specify delimiter, then y'all are non tied amongst comma. Apart from this 2 builtin part together with furnish seat of delimiter inward String, You ask to utilization Sybase SQL part LEFT() which furnish substring inward Sybase to a greater extent than just left of master copy string shape seat 1 to specified position. nosotros likewise ask to utilization part STUFF to update master copy String together with withdraw the outset substring out of it. STUFF allows y'all to delete characters from String together with attach specified characters. Here nosotros are non attaching anything together with passed goose egg to just delete graphic symbol from seat 1 to index of comma. In side past times side department nosotros volition meet illustration of splitting String inward Sybase together with Microsoft SQL Server using both CHARINDEX together with PATINDEX function.
Sybase CHARINDEX Example to Split String
Here is code illustration of How to separate string inward Sybase adaptive server using CHARINDEX function. This tin flame live on used inward whatever stored physical care for to separate whatever comma delimited String. In this illustration nosotros convey used CHARINDEX, LEFT together with STUFF part to separate 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)
spell @pos <> 0
begin
SET @piece = LEFT(@string, @pos-1)
impress @piece
SET @string = stuff(@string, 1, @pos, NULL)
SET @pos = charindex(',' , @string)
end
impress @string --this is required to impress concluding string
Output:
abc
xyx
def
SET @string = 'abc,xyx,def'
declare @pos numeric(20)
declare @piece varchar(50)
SET @pos = charindex(',' , @string)
spell @pos <> 0
begin
SET @piece = LEFT(@string, @pos-1)
impress @piece
SET @string = stuff(@string, 1, @pos, NULL)
SET @pos = charindex(',' , @string)
end
impress @string --this is required to impress concluding string
Output:
abc
xyx
def
How to separate string inward SQL Server using PATINDEX
In concluding department nosotros convey seen how to separate String inward stored physical care for on Sybase database using CHARINDEX part but nosotros tin flame likewise split String using PATINDEX function every bit shown inward below stored physical care for snippet. This stored physical care for snippet is non much dissimilar than previous one, just 2 %sign together with than graphic symbol (,) to specify pattern. Main difference betwixt PATINDEX together with CHARINDEX part inward Sybase is that PATINDEX supports wildcards inward search string which is non supported past times CHARINDEX function. Here is sample code to separate String using PATINDEX inward 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)
spell @pos <> 0
begin
SET @piece = LEFT(@string, @pos-1)
impress @piece
SET @string = stuff(@string, 1, @pos, NULL)
SET @pos = charindex(',' , @string)
end
impress @string --this is required to impress concluding string
Output:
abc
xyx
def
SET @string = 'abc,xyx,def'
declare @pos numeric(20)
declare @piece varchar(50)
SET @pos = patindex('%,%' , @string)
spell @pos <> 0
begin
SET @piece = LEFT(@string, @pos-1)
impress @piece
SET @string = stuff(@string, 1, @pos, NULL)
SET @pos = charindex(',' , @string)
end
impress @string --this is required to impress concluding string
Output:
abc
xyx
def
That’s all on How to separate String inward Stored physical care for inward Sybase or SQL Server. As I establish almost of mean value which plant on Sybase likewise plant on SQL Server, this stored physical care for snippet volition almost probable piece of job on both Sybase together with SQL Server. You tin flame non exclusively separate comma delimited String but likewise whatever other delimiter e.g. PIPE (|) or Colon [:] . After splitting String , You tin flame either impress the private string or insert them into table, its your choice.