August 23, 2017 at 12:28 pm
Hello SQL Gurus,
I need some help in extracting the data out of the string in certain way.
So basically between each pair of # there is a value stored for each day of the week starting from MondayDECLARE @FieldName VARCHAR(50)
SET @FieldName='#14400#14400#14400#14400#14400#0#0#';
SELECT ltrim(@FieldName)
Basically between each pair of # there is a value stored for each day of the week starting from Monday.
I need the above string to return data as such:
Monday Tuesday Wednesday Thursday Friday Saturday Sunday
14400 14400 14400 14400 14400 0 0
Thank you !!
DS
August 23, 2017 at 12:40 pm
DelimitedSplit8K would work, if you want that returned as a table (so each day of the week would be a record)...
August 23, 2017 at 3:01 pm
DECLARE
@FieldName VARCHAR(50) = '#14400#14400#14400#14400#14400#0#0#' ;
SELECT @FieldName;
SELECT
Substring(@FieldName, 2, 5) Monday
, Substring(@FieldName, 8, 5) Tuesday
, Substring(@FieldName, 14, 5) Wednesday
, Substring(@FieldName, 20, 5) Thursday
, Substring(@FieldName, 26, 5) Friday
, Substring(@FieldName, 32, 1) Saturday
, Substring(@FieldName, 34, 1) Sunday ;
August 23, 2017 at 7:25 pm
Joe Torre - Wednesday, August 23, 2017 3:01 PM
DECLARE
@FieldName VARCHAR(50) = '#14400#14400#14400#14400#14400#0#0#' ;
SELECT @FieldName;
SELECT
Substring(@FieldName, 2, 5) Monday
, Substring(@FieldName, 8, 5) Tuesday
, Substring(@FieldName, 14, 5) Wednesday
, Substring(@FieldName, 20, 5) Thursday
, Substring(@FieldName, 26, 5) Friday
, Substring(@FieldName, 32, 1) Saturday
, Substring(@FieldName, 34, 1) Sunday ;
Now try it with this:SET @FieldName='#1550000#2099#14400#154006#15400#0#10#';
:unsure:
-- Itzik Ben-Gan 2001
August 23, 2017 at 7:33 pm
As pietlinden said, delimitedsplit8K is the way to go. You could do something like this:
DECLARE @FieldName VARCHAR(50)
SET @FieldName='#1550000#2099#14400#154006#15400#0#10#';
SELECT
Mon = MAX(CASE ItemNumber WHEN 2 THEN Item END),
Tue = MAX(CASE ItemNumber WHEN 3 THEN Item END),
Wed = MAX(CASE ItemNumber WHEN 4 THEN Item END),
Thu = MAX(CASE ItemNumber WHEN 5 THEN Item END),
Fri = MAX(CASE ItemNumber WHEN 6 THEN Item END),
Sat = MAX(CASE ItemNumber WHEN 7 THEN Item END),
Sun = MAX(CASE ItemNumber WHEN 8 THEN Item END)
FROM DelimitedSplit8K(@FieldName, '#')
WHERE ItemNumber BETWEEN 2 AND 8;
Alternatively you could do a Cascading CROSS APPLY like this:
SELECT
Mon = SUBSTRING(t.fieldName, 1, d1.d-1),
Tue = SUBSTRING(t.fieldName, d1.d+1, (d2.d-d1.d)-1),
Wed = SUBSTRING(t.fieldName, d2.d+1, (d3.d-d2.d)-1),
Thu = SUBSTRING(t.fieldName, d3.d+1, (d4.d-d3.d)-1),
Fri = SUBSTRING(t.fieldName, d4.d+1, (d5.d-d4.d)-1),
Sat = SUBSTRING(t.fieldName, d5.d+1, (d6.d-d5.d)-1),
Sun = SUBSTRING(t.fieldName, d6.d+1, (d7.d-d6.d)-1)
FROM (VALUES (SUBSTRING(@fieldName, 2, 50))) t(fieldName)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, 1))) d1(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d1.d+1))) d2(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d2.d+1))) d3(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d3.d+1))) d4(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d4.d+1))) d5(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d5.d+1))) d6(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d6.d+1))) d7(d)
-- Itzik Ben-Gan 2001
August 24, 2017 at 2:09 pm
Alan.B - Wednesday, August 23, 2017 7:33 PMAs pietlinden said, delimitedsplit8K is the way to go. You could do something like this:
DECLARE @FieldName VARCHAR(50)
SET @FieldName='#1550000#2099#14400#154006#15400#0#10#';SELECT
Mon = MAX(CASE ItemNumber WHEN 2 THEN Item END),
Tue = MAX(CASE ItemNumber WHEN 3 THEN Item END),
Wed = MAX(CASE ItemNumber WHEN 4 THEN Item END),
Thu = MAX(CASE ItemNumber WHEN 5 THEN Item END),
Fri = MAX(CASE ItemNumber WHEN 6 THEN Item END),
Sat = MAX(CASE ItemNumber WHEN 7 THEN Item END),
Sun = MAX(CASE ItemNumber WHEN 8 THEN Item END)
FROM DelimitedSplit8K(@FieldName, '#')
WHERE ItemNumber BETWEEN 2 AND 8;
Alternatively you could do a Cascading CROSS APPLY like this:
SELECT
Mon = SUBSTRING(t.fieldName, 1, d1.d-1),
Tue = SUBSTRING(t.fieldName, d1.d+1, (d2.d-d1.d)-1),
Wed = SUBSTRING(t.fieldName, d2.d+1, (d3.d-d2.d)-1),
Thu = SUBSTRING(t.fieldName, d3.d+1, (d4.d-d3.d)-1),
Fri = SUBSTRING(t.fieldName, d4.d+1, (d5.d-d4.d)-1),
Sat = SUBSTRING(t.fieldName, d5.d+1, (d6.d-d5.d)-1),
Sun = SUBSTRING(t.fieldName, d6.d+1, (d7.d-d6.d)-1)
FROM (VALUES (SUBSTRING(@fieldName, 2, 50))) t(fieldName)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, 1))) d1(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d1.d+1))) d2(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d2.d+1))) d3(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d3.d+1))) d4(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d4.d+1))) d5(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d5.d+1))) d6(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d6.d+1))) d7(d)
Hi Alan,
This is great !! Thank you so much 🙂
One question, what if I have more data in my table such as below. How would I link it to the main query?
WITH SampleData(Fieldname) AS
(
SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#15500#0#14400#14400#14400#14400#0#'
UNION ALL SELECT '#0#0#15000#14400#14400#0#14400#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
)
SELECT *
FROM SampleData
Thanks again,
DS
August 24, 2017 at 3:22 pm
Alan.B - Wednesday, August 23, 2017 7:33 PMAs pietlinden said, delimitedsplit8K is the way to go. You could do something like this:
DECLARE @FieldName VARCHAR(50)
SET @FieldName='#1550000#2099#14400#154006#15400#0#10#';SELECT
Mon = MAX(CASE ItemNumber WHEN 2 THEN Item END),
Tue = MAX(CASE ItemNumber WHEN 3 THEN Item END),
Wed = MAX(CASE ItemNumber WHEN 4 THEN Item END),
Thu = MAX(CASE ItemNumber WHEN 5 THEN Item END),
Fri = MAX(CASE ItemNumber WHEN 6 THEN Item END),
Sat = MAX(CASE ItemNumber WHEN 7 THEN Item END),
Sun = MAX(CASE ItemNumber WHEN 8 THEN Item END)
FROM DelimitedSplit8K(@FieldName, '#')
WHERE ItemNumber BETWEEN 2 AND 8;
Alternatively you could do a Cascading CROSS APPLY like this:
SELECT
Mon = SUBSTRING(t.fieldName, 1, d1.d-1),
Tue = SUBSTRING(t.fieldName, d1.d+1, (d2.d-d1.d)-1),
Wed = SUBSTRING(t.fieldName, d2.d+1, (d3.d-d2.d)-1),
Thu = SUBSTRING(t.fieldName, d3.d+1, (d4.d-d3.d)-1),
Fri = SUBSTRING(t.fieldName, d4.d+1, (d5.d-d4.d)-1),
Sat = SUBSTRING(t.fieldName, d5.d+1, (d6.d-d5.d)-1),
Sun = SUBSTRING(t.fieldName, d6.d+1, (d7.d-d6.d)-1)
FROM (VALUES (SUBSTRING(@fieldName, 2, 50))) t(fieldName)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, 1))) d1(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d1.d+1))) d2(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d2.d+1))) d3(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d3.d+1))) d4(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d4.d+1))) d5(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d5.d+1))) d6(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d6.d+1))) d7(d)
Hi Alan,
This is great !! Thank you so much 🙂
One question, what if I have more data in my table such as below. How would I link it to the main query?
WITH SampleData(Fieldname) AS
(
SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#15500#0#14400#14400#14400#14400#0#'
UNION ALL SELECT '#0#0#15000#14400#14400#0#14400#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
)
SELECT *
FROM SampleData
.
No Problem.
Splitter approach:WITH SampleData(SomeId, Fieldname) AS
(
SELECT 1,'#14400#14400#84001#15400#14400#0#0#' UNION ALL
SELECT 2,'#15500#0#14433#14400#14400#4455#12#' UNION ALL
SELECT 3,'#0#0#15000#55400#14990#0#14400#' UNION ALL
SELECT 4,'#14400#14677#1800#14400#14400#0#0#' UNION ALL
SELECT 5,'#14400#990#14400#14411#14422#0#0#' UNION ALL
SELECT 6,'#14400#14400#14400#14400#14400#0#0#' UNION ALL
SELECT 7,'#2450#5400#99400#11404#195099#0#0#'
)
SELECT t.SomeId, split.*
FROM SampleData t
CROSS APPLY
(
SELECT
Mon = MAX(CASE ItemNumber WHEN 2 THEN Item END),
Tue = MAX(CASE ItemNumber WHEN 3 THEN Item END),
Wed = MAX(CASE ItemNumber WHEN 4 THEN Item END),
Thu = MAX(CASE ItemNumber WHEN 5 THEN Item END),
Fri = MAX(CASE ItemNumber WHEN 6 THEN Item END),
Sat = MAX(CASE ItemNumber WHEN 7 THEN Item END),
Sun = MAX(CASE ItemNumber WHEN 8 THEN Item END)
FROM DelimitedSplit8K(t.Fieldname, '#')
WHERE ItemNumber BETWEEN 2 AND 8
) split;
Using the cross tab approach:
WITH SampleData(SomeId, Fieldname) AS
(
SELECT 1,'#14400#14400#84001#15400#14400#0#0#' UNION ALL
SELECT 2,'#15500#0#14433#14400#14400#4455#12#' UNION ALL
SELECT 3,'#0#0#15000#55400#14990#0#14400#' UNION ALL
SELECT 4,'#14400#14677#1800#14400#14400#0#0#' UNION ALL
SELECT 5,'#14400#990#14400#14411#14422#0#0#' UNION ALL
SELECT 6,'#14400#14400#14400#14400#14400#0#0#' UNION ALL
SELECT 7,'#2450#5400#99400#11404#195099#0#0#'
)
SELECT t.SomeId, piv.*
FROM SampleData t
CROSS APPLY
(
SELECT
Mon = MAX(CASE ItemNumber WHEN 2 THEN Item END),
Tue = MAX(CASE ItemNumber WHEN 3 THEN Item END),
Wed = MAX(CASE ItemNumber WHEN 4 THEN Item END),
Thu = MAX(CASE ItemNumber WHEN 5 THEN Item END),
Fri = MAX(CASE ItemNumber WHEN 6 THEN Item END),
Sat = MAX(CASE ItemNumber WHEN 7 THEN Item END),
Sun = MAX(CASE ItemNumber WHEN 8 THEN Item END)
FROM DelimitedSplit8K(t.FieldName, '#')
WHERE ItemNumber BETWEEN 2 AND 8
) piv;
-- Itzik Ben-Gan 2001
August 24, 2017 at 6:08 pm
DiabloSlayer - Thursday, August 24, 2017 2:09 PMAlan.B - Wednesday, August 23, 2017 7:33 PMAs pietlinden said, delimitedsplit8K is the way to go. You could do something like this:
DECLARE @FieldName VARCHAR(50)
SET @FieldName='#1550000#2099#14400#154006#15400#0#10#';SELECT
Mon = MAX(CASE ItemNumber WHEN 2 THEN Item END),
Tue = MAX(CASE ItemNumber WHEN 3 THEN Item END),
Wed = MAX(CASE ItemNumber WHEN 4 THEN Item END),
Thu = MAX(CASE ItemNumber WHEN 5 THEN Item END),
Fri = MAX(CASE ItemNumber WHEN 6 THEN Item END),
Sat = MAX(CASE ItemNumber WHEN 7 THEN Item END),
Sun = MAX(CASE ItemNumber WHEN 8 THEN Item END)
FROM DelimitedSplit8K(@FieldName, '#')
WHERE ItemNumber BETWEEN 2 AND 8;
Alternatively you could do a Cascading CROSS APPLY like this:
SELECT
Mon = SUBSTRING(t.fieldName, 1, d1.d-1),
Tue = SUBSTRING(t.fieldName, d1.d+1, (d2.d-d1.d)-1),
Wed = SUBSTRING(t.fieldName, d2.d+1, (d3.d-d2.d)-1),
Thu = SUBSTRING(t.fieldName, d3.d+1, (d4.d-d3.d)-1),
Fri = SUBSTRING(t.fieldName, d4.d+1, (d5.d-d4.d)-1),
Sat = SUBSTRING(t.fieldName, d5.d+1, (d6.d-d5.d)-1),
Sun = SUBSTRING(t.fieldName, d6.d+1, (d7.d-d6.d)-1)
FROM (VALUES (SUBSTRING(@fieldName, 2, 50))) t(fieldName)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, 1))) d1(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d1.d+1))) d2(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d2.d+1))) d3(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d3.d+1))) d4(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d4.d+1))) d5(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d5.d+1))) d6(d)
CROSS APPLY (VALUES (CHARINDEX('#', t.fieldName, d6.d+1))) d7(d)Hi Alan,
This is great !! Thank you so much 🙂
One question, what if I have more data in my table such as below. How would I link it to the main query?
WITH SampleData(Fieldname) AS
(
SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#15500#0#14400#14400#14400#14400#0#'
UNION ALL SELECT '#0#0#15000#14400#14400#0#14400#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
UNION ALL SELECT '#14400#14400#14400#14400#14400#0#0#'
)SELECT *
FROM SampleData
.
No Problem.
Splitter approach:WITH SampleData(SomeId, Fieldname) AS
(
SELECT 1,'#14400#14400#84001#15400#14400#0#0#' UNION ALL
SELECT 2,'#15500#0#14433#14400#14400#4455#12#' UNION ALL
SELECT 3,'#0#0#15000#55400#14990#0#14400#' UNION ALL
SELECT 4,'#14400#14677#1800#14400#14400#0#0#' UNION ALL
SELECT 5,'#14400#990#14400#14411#14422#0#0#' UNION ALL
SELECT 6,'#14400#14400#14400#14400#14400#0#0#' UNION ALL
SELECT 7,'#2450#5400#99400#11404#195099#0#0#'
)
SELECT t.SomeId, split.*
FROM SampleData t
CROSS APPLY
(
SELECT
Mon = MAX(CASE ItemNumber WHEN 2 THEN Item END),
Tue = MAX(CASE ItemNumber WHEN 3 THEN Item END),
Wed = MAX(CASE ItemNumber WHEN 4 THEN Item END),
Thu = MAX(CASE ItemNumber WHEN 5 THEN Item END),
Fri = MAX(CASE ItemNumber WHEN 6 THEN Item END),
Sat = MAX(CASE ItemNumber WHEN 7 THEN Item END),
Sun = MAX(CASE ItemNumber WHEN 8 THEN Item END)
FROM DelimitedSplit8K(t.Fieldname, '#')
WHERE ItemNumber BETWEEN 2 AND 8
) split;
Using the cross tab approach:
WITH SampleData(SomeId, Fieldname) AS
(
SELECT 1,'#14400#14400#84001#15400#14400#0#0#' UNION ALL
SELECT 2,'#15500#0#14433#14400#14400#4455#12#' UNION ALL
SELECT 3,'#0#0#15000#55400#14990#0#14400#' UNION ALL
SELECT 4,'#14400#14677#1800#14400#14400#0#0#' UNION ALL
SELECT 5,'#14400#990#14400#14411#14422#0#0#' UNION ALL
SELECT 6,'#14400#14400#14400#14400#14400#0#0#' UNION ALL
SELECT 7,'#2450#5400#99400#11404#195099#0#0#'
)
SELECT t.SomeId, piv.*
FROM SampleData t
CROSS APPLY
(
SELECT
Mon = MAX(CASE ItemNumber WHEN 2 THEN Item END),
Tue = MAX(CASE ItemNumber WHEN 3 THEN Item END),
Wed = MAX(CASE ItemNumber WHEN 4 THEN Item END),
Thu = MAX(CASE ItemNumber WHEN 5 THEN Item END),
Fri = MAX(CASE ItemNumber WHEN 6 THEN Item END),
Sat = MAX(CASE ItemNumber WHEN 7 THEN Item END),
Sun = MAX(CASE ItemNumber WHEN 8 THEN Item END)
FROM DelimitedSplit8K(t.FieldName, '#')
WHERE ItemNumber BETWEEN 2 AND 8
) piv;
Hi Alan,
You are just too good (honestly) 🙂
Thanks a million,
Take care,
DS
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply