How To Carve Upward String Inwards Sql Server Together With Sybase
Some fourth dimension nosotros necessitate to dissever a long comma separated String inwards 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 in addition to than afterward dissever comma separated String into multiple values within stored proc. This is non just instance of input parameter but yous tin give the axe too accept comma separated string inwards whatsoever tabular array data. Unfortunately at that topographic point is no split() business office inwards Sybase or SQL Server 2005 or 2008 which tin give the axe straight dissever string based on delimiter just similar inwards Java string dissever method. Fortunately Sybase Adaptive Server in addition to Microsoft SQL server has functions similar CHARINDEX in addition to PATINDEX which tin give the axe last used to dissever comma separated String. This is adjacent on our SQL tutorials after seeing SQL inquiry to discovery duplicate records inwards table in addition to How to discovery 2nd in addition to Nth maximum salary inwards SQL.
By the agency both CHARINDEX() in addition to PATINDEX() allows to specify delimiter, then yous are non tied amongst comma. Apart from this ii builtin business office in addition to furnish seat of delimiter inwards String, You necessitate to work Sybase SQL business office LEFT() which furnish substring inwards Sybase to a greater extent than exactly left of master copy string shape seat 1 to specified position. nosotros too necessitate to work business office STUFF to update master copy String in addition to take away the get-go substring out of it. STUFF allows yous to delete characters from String in addition to attach specified characters. Here nosotros are non attaching anything in addition to passed naught to just delete grapheme from seat 1 to index of comma. In adjacent department nosotros volition come across instance of splitting String inwards Sybase in addition to Microsoft SQL Server using both CHARINDEX in addition to PATINDEX function.
Sybase CHARINDEX Example to Split String
Here is code instance of How to dissever string inwards Sybase adaptive server using CHARINDEX function. This tin give the axe last used inwards whatsoever stored physical care for to dissever whatsoever comma delimited String. In this instance nosotros accept used CHARINDEX, LEFT in addition to STUFF business office to dissever 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 terminal 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 terminal string
Output:
abc
xyx
def
How to dissever string inwards SQL Server using PATINDEX
In terminal department nosotros accept seen how to dissever String inwards stored physical care for on Sybase database using CHARINDEX business office but nosotros tin give the axe too split String using PATINDEX function every bit shown inwards below stored physical care for snippet. This stored physical care for snippet is non much dissimilar than previous one, just ii %sign in addition to than grapheme (,) to specify pattern. Main difference betwixt PATINDEX in addition to 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 dissever 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)
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 terminal 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 terminal string
Output:
abc
xyx
def
That’s all on How to dissever String inwards Stored physical care for inwards Sybase or SQL Server. As I flora nearly of intend which plant on Sybase too plant on SQL Server, this stored physical care for snippet volition nearly probable piece of work on both Sybase in addition to SQL Server. You tin give the axe non entirely dissever comma delimited String but too whatsoever other delimiter e.g. PIPE (|) or Colon [:] . After splitting String , You tin give the axe either impress the private string or insert them into table, its your choice.
Belum ada Komentar untuk "How To Carve Upward String Inwards Sql Server Together With Sybase"
Posting Komentar