February 18, 2011 at 10:01 am
I have a query that selects a sinlge record, with a column called order_string. When I select order_id, order_string from my table, I get something like this:
1, Bargain=1250=0D===2&Child/Senior=1175=0E===2
I need to parse the order_string column to pull out the individual tickets, so that my result looks like this:
1, Bargain,1250,2
1, Child/Senior,1175,2
Keeping in mind that the order_string could contain 1 or many individual tickets within the string. I've been trying to put together a user-defined function to do this, but so far I'm just not getting it. Can anyone point me in the right direction?
February 18, 2011 at 5:58 pm
I'd use the following approach:
Step 1: remove the string before the three equal signs (including two equal signs)
Step 2: replace all equal signs with commas
Step 3: call a string split function to separate the rows based on the row delimiter
Step 4: call the string split function to separate the columns based on the comma
February 22, 2011 at 4:55 pm
Thanks for the reply Lutz, It helped get me down the right path. Here is what I did:
1) created a user defined function that accepts an input field and a delimiter
2) cross apply on the function, to split into a separate record where there is an &
3) various string functions to split out movie name, price and quantity from each record
Here is a basic rundown:
The function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[F_parse_string](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
Thanks to logiclabz.com for the idea for the function: http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
The query:
SELECT Left( r.items, Charindex ( ',', r.items ) - 1 )
, Cast( Substring( r.items, Charindex ( ',', r.items ) + 1, Sum( ( Charindex ( ',', r.items, Charindex ( ',', r.items ) + 1 ) + 1 ) ) - ( Charindex ( ',', r.items ) + 2 ) ) AS NUMERIC ) * .01
, order_total * .01
, Right( r.items, Charindex ( ',', Reverse( r.items ) ) - 1 )
FROM orders o
CROSS APPLY dbo.F_parse_string ( Replace( Replace( Replace( order_tickets, '===', ',' ), '==', '=' ), '=', ',' ), '&' ) AS r
GROUP BY r.items
, order_total
February 22, 2011 at 5:12 pm
please search this site for DelimitedSplit8K. It will point you to a function that will be much more efficient than your loop. The function mentioned above could be used as an inline table-valued function increasing performance even more.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply