How To Dissever String Inward Sql Server Together With Sybase
Wednesday, June 13, 2018
Add Comment
Some fourth dimension nosotros postulate to split upwardly a long comma separated String inwards Stored physical care for e.g. Sybase or SQL Server stored procedures. Its quite mutual to overstep comma delimited or delimiter separated String every bit input parameter to Stored physical care for together with than afterwards split upwardly comma separated String into multiple values within stored proc. This is non just instance of input parameter but y'all tin give the axe likewise convey comma separated string inwards whatever tabular array data. Unfortunately at that topographic point is no split() role inwards Sybase or SQL Server 2005 or 2008 which tin give the axe conduct split upwardly string based on delimiter just similar inwards Java string split upwardly method. Fortunately Sybase Adaptive Server together with Microsoft SQL server has functions similar CHARINDEX together with PATINDEX which tin give the axe hold upwardly used to split upwardly comma separated String. This is adjacent on our SQL tutorials after seeing SQL enquiry to uncovering duplicate records inwards table together with How to uncovering 2nd together with Nth maximum salary inwards SQL.
By the agency both CHARINDEX() together with PATINDEX() allows to specify delimiter, together with thence y'all are non tied amongst comma. Apart from this ii builtin role together with provide seat of delimiter inwards String, You postulate to exercise Sybase SQL role LEFT() which provide substring inwards Sybase to a greater extent than exactly left of master copy string shape seat 1 to specified position. nosotros likewise postulate to exercise role STUFF to update master copy String together with take the commencement 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 cypher to merely delete grapheme from seat 1 to index of comma. In adjacent department nosotros volition run across instance 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 instance of How to split upwardly string inwards Sybase adaptive server using CHARINDEX function. This tin give the axe hold upwardly used inwards whatever stored physical care for to split upwardly whatever comma delimited String. In this instance nosotros convey used CHARINDEX, LEFT together with STUFF role to split upwardly 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)
land @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)
land @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 split upwardly string inwards SQL Server using PATINDEX
In concluding department nosotros convey seen how to split upwardly String inwards stored physical care for on Sybase database using CHARINDEX role but nosotros tin give the axe likewise 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 together with than grapheme (,) to specify pattern. Main difference betwixt PATINDEX together with CHARINDEX role inwards Sybase is that PATINDEX supports wildcards inwards search string which is non supported past times CHARINDEX function. Here is sample code to split upwardly 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)
land @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)
land @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 split upwardly String inwards Stored physical care for inwards Sybase or SQL Server. As I establish close of intend which plant on Sybase likewise plant on SQL Server, this stored physical care for snippet volition close probable piece of employment on both Sybase together with SQL Server. You tin give the axe non solely split upwardly comma delimited String but likewise whatever 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.
0 Response to "How To Dissever String Inward Sql Server Together With Sybase"
Post a Comment