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 Monday

    DECLARE @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

    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)

    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

  • DiabloSlayer - Thursday, August 24, 2017 2:09 PM

    Alan.B - Wednesday, August 23, 2017 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)

    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;

    "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

    DiabloSlayer - Thursday, August 24, 2017 2:09 PM

    Alan.B - Wednesday, August 23, 2017 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)

    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