How To Split Upwards String Inwards Sql Server Too Sybase
Some fourth dimension nosotros involve to divide a long comma separated String inwards Stored physical care for e.g. Sybase or SQL Server stored procedures. Its quite mutual to move yesteryear comma delimited or delimiter separated String equally input parameter to Stored physical care for together with than afterward divide comma separated String into multiple values within stored proc. This is non just illustration of input parameter but you lot tin strength out equally good bring comma separated string inwards whatever tabular array data. Unfortunately at that spot is no split() business office inwards Sybase or SQL Server 2005 or 2008 which tin strength out straight divide string based on delimiter just similar inwards Java string divide method. Fortunately Sybase Adaptive Server together with Microsoft SQL server has functions similar CHARINDEX together with PATINDEX which tin strength out last used to divide comma separated String. This is side yesteryear side on our SQL tutorials after seeing SQL enquiry to abide by duplicate records inwards table together with How to abide by 2nd together with Nth maximum salary inwards SQL.
By the agency both CHARINDEX() together with PATINDEX() allows to specify delimiter, together with hence you lot are non tied alongside comma. Apart from this 2 builtin business office together with supply seat of delimiter inwards String, You involve to utilisation Sybase SQL business office LEFT() which supply substring inwards Sybase to a greater extent than exactly left of master copy string cast seat 1 to specified position. nosotros equally good involve to utilisation business office STUFF to update master copy String together with take away the start substring out of it. STUFF allows you lot to delete characters from String together with attach specified characters. Here nosotros are non attaching anything together with passed nil to but delete graphic symbol from seat 1 to index of comma. In side yesteryear side department nosotros volition run across illustration of splitting String inwards 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 divide string inwards Sybase adaptive server using CHARINDEX function. This tin strength out last used inwards whatever stored physical care for to divide whatever comma delimited String. In this illustration nosotros bring used CHARINDEX, LEFT together with STUFF business office to divide 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)
acre @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 final string
Output:
abc
xyx
def
SET @string = 'abc,xyx,def'
declare @pos numeric(20)
declare @piece varchar(50)
SET @pos = charindex(',' , @string)
acre @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 final string
Output:
abc
xyx
def
How to divide string inwards SQL Server using PATINDEX
In final department nosotros bring seen how to divide String inwards stored physical care for on Sybase database using CHARINDEX business office but nosotros tin strength out equally good split String using PATINDEX function equally shown inwards 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 business office inwards Sybase is that PATINDEX supports wildcards inwards search string which is non supported yesteryear CHARINDEX function. Here is sample code to divide String using PATINDEX inwards 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)
acre @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 final string
Output:
abc
xyx
def
SET @string = 'abc,xyx,def'
declare @pos numeric(20)
declare @piece varchar(50)
SET @pos = patindex('%,%' , @string)
acre @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 final string
Output:
abc
xyx
def
That’s all on How to divide String inwards Stored physical care for inwards Sybase or SQL Server. As I constitute near of mean value which industrial plant on Sybase equally good industrial plant on SQL Server, this stored physical care for snippet volition near probable piece of job on both Sybase together with SQL Server. You tin strength out non entirely divide comma delimited String but equally good whatever other delimiter e.g. PIPE (|) or Colon [:] . After splitting String , You tin strength out either impress the private string or insert them into table, its your choice.
0 Response to "How To Split Upwards String Inwards Sql Server Too Sybase"
Post a Comment