February 9, 2012 at 12:12 am
Comments posted to this topic are about the item Get Line From Paragraph
February 9, 2012 at 4:49 pm
You may be interested in using a more generic function to do this that provides additional flexibility. I use a function that splits a string according to a user specified delimiter. Here's some example code:
DECLARE @NL VARCHAR(1), @cr VARCHAR(1), @rc INT;
SELECT @NL = char(10), @cr = char(13), @rc = -1;
Declare @csv varchar(max);
Select @csv =
'2011-11-03 07:59:00,109,AH,1,,6.8,3.2,3.1,4.9,5.3,5.8,5.9,5.8,5.3,5.4,5.3,5.6,5.7,6,5.9,5.4,5.1,5,5.9,6.2,7.3,6.6,7' + @NL +
'2011-11-03 07:51:00,214,AH,1,5.8,3.3,5.7,6.3,6.1,7,7,6.1,5.8,5.7,5.1,4.9,5.4,5.8,6.4,6.8,6,7.5,6.8,5.6,5.6,5.5,3.2,2.8' + @NL +
'2011-11-03 07:50:00,217,AH,1,4.3,,,,,,,,,,,,,,,,,,,,,,,' + @NL +
'2011-11-03 07:49:00,218,AH,1,4.8,5.3,4.9,5.1,7.2,6,5.5,5.7,5.3,4.8,5.1,4.8,5.3,5.8,5.6,6.2,6.2,6.4,5.8,5.9,5.6,3.9,4.1,4.7' + @NL +
'2011-11-03 07:47:00,236,RT,1,,,,,,,,,,,,,,,3.8,4.4,,,,,,,,' + @NL +
'2011-11-03 07:46:00,237,AH,1,5.9,5.3,4.5,4.6,5.2,5.7,5.6,6.2,5.4,5.4,5.3,5.3,5.6,5.3,3.2,4.7,5.3,5.2,5.7,5.2,5.3,5.6,5.5,5.7' + @NL +
'2011-11-03 07:45:00,238,RT,1,,,,,,,,,,,,,,,3.7,4.1,,,,,,,,' + @NL +
'2011-11-03 07:43:00,243,RT,1,,,,,,,,,,,,,3.4,3.7,,,,,,,,,,' + @NL +
'2011-11-03 07:43:00,242,AH,1,5.2,5.3,5.2,5.2,5.4,5.9,5.6,5.9,5.6,5.6,5,5.1,2.2,4.8,5.7,6.3,5.8,5.1,5.5,5,5.7,6,6.8,5.9' + @NL +
'2011-11-03 07:44:00,246,RT,1,,,,,,,,,,,,,,,,,,,,,,,3.6,3.5' + @NL;
-- We're expecting '' (char(10) line-delimiters, so remove any '\r' delimiters
Select @csv = REPLACE(@csv,@cr, '');
-- Replace consequtive commas with comma-space-comma so that the split yields ' '
Select @csv = REPLACE(@csv,',', ', ');
Select * from dbo.ufn_ParseArray(@csv, @NL, 1);
The output looks like this:
[highlight="#DDDDDD"]
Row Val
------------------- ----------------------------------------------------------------------------------------------------------------
1 2011-11-03 07:59:00, 109, AH, 1, , 6.8, 3.2, 3.1, 4.9, 5.3, 5.8, 5.9, 5.8, 5.3, 5.4, 5.3, 5.6, 5.7, 6, 5.9, 5.4, 5.1, 5, 5.9, 6.2, 7.3, 6.6, 7
2 2011-11-03 07:51:00, 214, AH, 1, 5.8, 3.3, 5.7, 6.3, 6.1, 7, 7, 6.1, 5.8, 5.7, 5.1, 4.9, 5.4, 5.8, 6.4, 6.8, 6, 7.5, 6.8, 5.6, 5.6, 5.5, 3.2, 2.8
3 2011-11-03 07:50:00, 217, AH, 1, 4.3, , , , , , , , , , , , , , , , , , , , , , ,
4 2011-11-03 07:49:00, 218, AH, 1, 4.8, 5.3, 4.9, 5.1, 7.2, 6, 5.5, 5.7, 5.3, 4.8, 5.1, 4.8, 5.3, 5.8, 5.6, 6.2, 6.2, 6.4, 5.8, 5.9, 5.6, 3.9, 4.1, 4.7
5 2011-11-03 07:47:00, 236, RT, 1, , , , , , , , , , , , , , , 3.8, 4.4, , , , , , , ,
6 2011-11-03 07:46:00, 237, AH, 1, 5.9, 5.3, 4.5, 4.6, 5.2, 5.7, 5.6, 6.2, 5.4, 5.4, 5.3, 5.3, 5.6, 5.3, 3.2, 4.7, 5.3, 5.2, 5.7, 5.2, 5.3, 5.6, 5.5, 5.7
7 2011-11-03 07:45:00, 238, RT, 1, , , , , , , , , , , , , , , 3.7, 4.1, , , , , , , ,
8 2011-11-03 07:43:00, 243, RT, 1, , , , , , , , , , , , , 3.4, 3.7, , , , , , , , , ,
9 2011-11-03 07:43:00, 242, AH, 1, 5.2, 5.3, 5.2, 5.2, 5.4, 5.9, 5.6, 5.9, 5.6, 5.6, 5, 5.1, 2.2, 4.8, 5.7, 6.3, 5.8, 5.1, 5.5, 5, 5.7, 6, 6.8, 5.9
10 2011-11-03 07:44:00, 246, RT, 1, , , , , , , , , , , , , , , , , , , , , , , 3.6, 3.5
[/highlight]
Changing the query to this:
Select Val from dbo.ufn_ParseArray(@csv, @NL, 1) Where Row = 4;);
Yields this: [highlight="#DDDDDD"]
Val
2011-11-03 07:49:00, 218, AH, 1, 4.8, 5.3, 4.9, 5.1, 7.2, 6, 5.5, 5.7, 5.3, 4.8, 5.1, 4.8, 5.3, 5.8, 5.6, 6.2, 6.2, 6.4, 5.8, 5.9, 5.6, 3.9, 4.1, 4.7
[/highlight]
Since the string is actually line-delimited csv data, then it's possible to split to individual variables using CROSS APPLY like this:
Select Lns.Row as Line, Vars.Row as VarNo, Case When Vars.Val = '' Then Null Else Vars.Val End as Val
from dbo.ufn_ParseArray(@csv, @NL, 1) as Lns
CROSS APPLY dbo.ufn_ParseArray(Val, ',', 1) as Vars;
Yields this: [highlight="#DDDDDD"]
Line VarNo Val
------------------- -------------------- -------------------------------------------
1 1 2011-11-03 07:59:00
1 2 109
1 3 AH
1 4 1
1 5 NULL
1 6 6.8
1 7 3.2
1 8 3.1
1 9 4.9
1 10 5.3
1 11 5.8
1 12 5.9
1 13 5.8
1 14 5.3
1 15 5.4
1 16 5.3
1 17 5.6
1 18 5.7
1 19 6
1 20 5.9
1 21 5.4
1 22 5.1
1 23 5
1 24 5.9
1 25 6.2
1 26 7.3
1 27 6.6
1 28 7
2 1 2011-11-03 07:51:00
2 2 214
2 3 AH
2 4 1
2 5 5.8
2 6 3.3
2 7 5.7
2 8 6.3
2 9 6.1
2 10 7
2 11 7
2 12 6.1
2 13 5.8
2 14 5.7
2 15 5.1
2 16 4.9
2 17 5.4
2 18 5.8
2 19 6.4
2 20 6.8
2 21 6
2 22 7.5
2 23 6.8
2 24 5.6
2 25 5.6
2 26 5.5
2 27 3.2
2 28 2.8
3 1 2011-11-03 07:50:00
3 2 217
3 3 AH
3 4 1
3 5 4.3
3 6 NULL
3 7 NULL
3 8 NULL
3 9 NULL
3 10 NULL
3 11 NULL
3 12 NULL
etc (280 rows in total) [/highlight]
Here's the code for the splitter function and Tally Table Function:
-----------------------------------------------------------------------------------
--Author:Lynn Pettis
--Date:29-Oct-2009
--Purpose:Generate a sequence of numbers efficiently.
--
--see:http://www.sqlservercentral.com/articles/T-SQL/67899/
--
--Input:
--@pStart BigintThe starting number of the returned sequence
--@pEnd BigintThe last number of the returned sequence
--@pInc BigintThe step between successive values
--
--Output
--A set of ordered integers (@pStart..@pEnd, step = @pInc)
--
-----------------------------------------------------------------------------------
CREATE function [dbo].[fn_al_Tally](
@pStart bigint= 0,
@pEnd bigint= 10000,
@pInc bigint= 1
)
returns table
as
return(
with BaseNum (
N
) as (
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1
),
L1 (
N
) as (
select
bn1.N
from
BaseNum bn1
cross join BaseNum bn2
),
L2 (
N
) as (
select
a1.N
from
L1 a1
cross join L1 a2
),
L3 (
N
) as (
select top ((abs(case when @pStart < @pEnd
then @pEnd
else @pStart
end -
case when @pStart < @pEnd
then @pStart
else @pEnd
end))/abs(@pInc)+ 1)
a1.N
from
L2 a1
cross join L2 a2
),
Tally (
N
) as (
select
row_number()over (order by a1.N)
from
L3 a1
)
select
((N - 1) * @pInc) + @pStart as N
from
Tally
);
----------------------------------------------------------------------------
--Author:Lifted from the net - origin is uncertain
--Date:3-Nov-2011
--Purpose: Split a list of delimited variables into a table
--
--Args:
--@Input varchar(max): The list to be split
--@Delimiter char(1): The field delimiter character
--@BaseRowNum int: The base Row number to be included in the result
--
--Output:
--A Table with [Row] & [Val] fields
--
----------------------------------------------------------------------------
Create FUNCTION [dbo].[ufn_ParseArray]
( @Input VARCHAR(max),
@Delimiter CHAR(1) = ',',
@BaseRowNum INT
)
RETURNS TABLE AS
RETURN
( SELECT ROW_NUMBER() OVER (ORDER BY n) + (@BaseRowNum - 1) [Row],
SUBSTRING(@Input, n, CHARINDEX(@Delimiter, @Input + @Delimiter, n) - n) Val
FROM (SELECT n from dbo.fn_al_Tally(0, LEN(@Input)+1, 1)) a
WHERE SUBSTRING(@Delimiter + @Input, n, 1) = @Delimiter AND (CHARINDEX(@Delimiter, @Input + @Delimiter, n) - n) != 0
);
May 11, 2016 at 7:07 am
Thanks for the script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply