SQL 2008 - Extract parts of a string
-
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
-
DelimitedSplit8K would work, if you want that returned as a table (so each day of the week would be a record)...
-
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 ;
- 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:"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
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)"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
- Alan.B - Wednesday, August 23, 2017 7:33 PM
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 SampleDataThanks again,
DS
- DiabloSlayer - Thursday, August 24, 2017 2:09 PM
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;"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
- Alan.B - Thursday, August 24, 2017 3:22 PM
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