How To Separate String Inward Sql Server In Addition To Sybase
Some fourth dimension nosotros require to divide a long comma separated String inwards Stored physical care for e.g. Sybase or SQL Server stored procedures. Its quite mutual to travel past times comma delimited or delimiter separated String every bit 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 instance of input parameter but yous tin every bit good convey comma separated string inwards whatever tabular array data. Unfortunately in that place is no split() business office inwards Sybase or SQL Server 2005 or 2008 which tin 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 live on used to divide comma separated String. This is side past times side on our SQL tutorials after seeing SQL enquiry to discovery duplicate records inwards table together with How to discovery 2nd together with Nth maximum salary inwards SQL.
By the agency both CHARINDEX() together with PATINDEX() allows to specify delimiter, together with thus yous are non tied amongst comma. Apart from this 2 builtin business office together with provide seat of delimiter inwards String, You require to utilization Sybase SQL business office LEFT() which provide substring inwards Sybase to a greater extent than just left of master copy string shape seat 1 to specified position. nosotros every bit good require to utilization business office STUFF to update master copy String together with withdraw the showtime substring out of it. STUFF allows yous to delete characters from String together with attach specified characters. Here nosotros are non attaching anything together with passed nix to just delete graphic symbol from seat 1 to index of comma. In side past times 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 live on used inwards whatever stored physical care for to divide whatever comma delimited String. In this illustration nosotros convey 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)
piece @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)
piece @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 divide string inwards SQL Server using PATINDEX
In concluding department nosotros convey seen how to divide String inwards stored physical care for on Sybase database using CHARINDEX business office but nosotros tin every bit good split String using PATINDEX function every bit shown inwards below stored physical care for snippet. This stored physical care for snippet is non much unlike 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 past times 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)
piece @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)
piece @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 divide String inwards Stored physical care for inwards Sybase or SQL Server. As I flora virtually of intend which industrial plant on Sybase every bit good industrial plant on SQL Server, this stored physical care for snippet volition virtually probable piece of occupation on both Sybase together with SQL Server. You tin non exclusively divide comma delimited String but every bit good whatever other delimiter e.g. PIPE (|) or Colon [:] . After splitting String , You tin either impress the private string or insert them into table, its your choice.
Komentar
Posting Komentar