December 9, 2011 at 2:13 pm
Hi ,
Can some one help me with the below issue.
I got a Table with one Column with data like ABC$$$DEF$EF
Now i want to split that data into another table three columns like ABC into one column DEF into another column and EF into another column
Or else a query which splits them into three peices excluding '$'
Thank you
December 28, 2011 at 7:11 am
use the loop and insert into new table . Check with following code
declare @temp varchar(100)
select @temp ='ABC$$$DEF$EF'
select * from SplitDelimited ((replace(replace(replace(@temp,'$$$','-'),'$$','-'),'$','-')),'-')
if you want split methode , you can use the following one.
Create FUNCTION [dbo].[SplitDelimited]
(
@List nvarchar(2000),
@SplitOn nvarchar(1)
)
RETURNS @RtnValue table (
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
December 28, 2011 at 7:52 am
pdharmaraju (12/28/2011)
use the loop and insert into new table
It has been shown repeatedly that the loop performs much worse than the string splitter that was linked to previously in this thread. See the following article about tally tables The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url] which includes a direct comparison of splitting strings by both a loop and a tally table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 29, 2011 at 4:30 pm
A lot of the solution depends on the data. If you replace '$$$' with '-' and '-' is an allowed character in the string, then you have trouble, so be careful with the replace. If all you know is that there are three sets of characters that do not include any '$', but are separated by the '$$$' and '$', then I might start simple with substring and charindex. My example doesn't include cases where there might be missing sets of characters (for example just 'ABC$$$DEF'), but those could be added with a case statement in each of the substring selections. Here is something I would try to start:
declare @temp varchar(100)
select @temp ='ABC$$$DEF$EF'
select @temp, SUBSTRING(@temp, 1, charindex('$$$', @temp)-1) as First_Set,
SUBSTRING(@temp, charindex('$$$', @temp) + 3, charindex('$', @temp, charindex('$$$', @temp)+ 3) - charindex('$$$', @temp) -3) as Middle_Set,
SUBSTRING(@temp, charindex('$', @temp, charindex('$$$', @temp)+ 3) + 1, LEN(@temp) -charindex('$', @temp, charindex('$$$', @temp)+ 3)) as Last_Set
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply