Split value according to delminitor [Resolved]

Posted by Santosh.Choudhury under Sql Server on 11/11/2014 | Points: 10 | Views : 582 | Status : [Member] | Replies : 3
I have one field as like route the data inside as like(hyd-bang-hyd) i need the result it into two row in first row from is hyd to is bang and 2nd row from is bang and to hyd .the data is not specfic length it may be 4 as like (hyd-pune-mumbai-hyd)this case it should be 3 rows.from hyd to pune in first row and 2nd row from pune to mumbai and 3rd row from mumbai and to hyd




Responses

Posted by: Bandi on: 11/13/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

-- Sample Data
create table split (col varchar(100))
insert split valueS('hyd-pune-mumbai-hyd'),('hyd-bang-hyd')

--Create UDF to split the string
/****** Object: UserDefinedFunction [dbo].[ParseValues] Script Date: 13-11-2014 PM 03:37:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
GO

-- Query to get the result

;with cte as (
SELECT * FROM split
CROSS APPLY [dbo].[ParseValues](col, '-'))
SELECT c1.col, c1.val+ ' to ' + c2.Val FROM cte c1
join cte c2 ON c1.col = c2.col and c1.id = c2.id-1


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Santosh.Choudhury, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nandkishorrech on: 11/11/2014 [Member] Starter | Points: 25

Up
0
Down
Hi Santosh,
Try this Code. Its Work dynamically according to your requirement.


string route = "Hyd-Bang-Pune-Mumbai-Hyd";
if (route.Length > 0)
{
string[] routes = route.Split('-').ToArray();
List<string> routeMap = new List<string>();
if (routes.Count() > 0)
{
for (int i = 0; i < routes.Count() - 1; i++)
{
string rt = routes[i] + "-" + routes[++i];
i--;
routeMap.Add(rt);
Console.WriteLine(rt);
}
}
Console.ReadKey();
}



Regards
Nanda Kishore.CH

Santosh.Choudhury, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Santosh.Choudhury on: 11/14/2014 [Member] Starter | Points: 25

Up
0
Down
Thank u ..


Santosh.Choudhury, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response