How To Divide String Inwards Sql Server As Well As Sybase
Some fourth dimension nosotros postulate to split upward a long comma separated String inwards Stored physical care for e.g. Sybase or SQL Server stored procedures. Its quite mutual to transcend comma delimited or delimiter separated String equally input parameter to Stored physical care for too than after split upward comma separated String into multiple values within stored proc. This is non just instance of input parameter but you lot tin too 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 straight split upward string based on delimiter just similar inwards Java string split upward method. Fortunately Sybase Adaptive Server too Microsoft SQL server has functions similar CHARINDEX too PATINDEX which tin endure used to split upward comma separated String. This is side past times side on our SQL tutorials after seeing SQL interrogation to honour duplicate records inwards table too How to honour 2nd too Nth maximum salary inwards SQL.
By the means both CHARINDEX() too PATINDEX() allows to specify delimiter, then you lot are non tied amongst comma. Apart from this ii builtin role too furnish seat of delimiter inwards String, You postulate to utilization Sybase SQL role LEFT() which furnish substring inwards Sybase to a greater extent than exactly left of master copy string cast seat 1 to specified position. nosotros too postulate to utilization role STUFF to update master copy String too take the commencement substring out of it. STUFF allows you lot to delete characters from String too attach specified characters. Here nosotros are non attaching anything too passed goose egg to just delete grapheme from seat 1 to index of comma. In side past times side department nosotros volition run into illustration of splitting String inwards Sybase too Microsoft SQL Server using both CHARINDEX too PATINDEX function.
Sybase CHARINDEX Example to Split String
Here is code illustration of How to split upward string inwards Sybase adaptive server using CHARINDEX function. This tin endure used inwards whatever stored physical care for to split upward whatever comma delimited String. In this illustration nosotros convey used CHARINDEX, LEFT too STUFF role to split upward 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 final 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 final string
Output:
abc
xyx
def
How to split upward string inwards SQL Server using PATINDEX
In final department nosotros convey seen how to split upward String inwards stored physical care for on Sybase database using CHARINDEX role but nosotros tin too 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 ii %sign too than grapheme (,) to specify pattern. Main difference betwixt PATINDEX too 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 upward 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 final 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 final string
Output:
abc
xyx
def
That’s all on How to split upward String inwards Stored physical care for inwards Sybase or SQL Server. As I constitute close of intend which industrial plant on Sybase too industrial plant on SQL Server, this stored physical care for snippet volition close probable piece of job on both Sybase too SQL Server. You tin non solely split upward comma delimited String but too 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.
0 Response to "How To Divide String Inwards Sql Server As Well As Sybase"
Post a Comment