What would be the best way to get the date from the following strings?

  • Please review the following examples of product names:

    UMA Titanium Temporary Hex Cylinder Product Code: 11-000300 <span style="color:#f00;">Availability: 6/15/2012</span>

    UMA Hex Cylinder Product Code: 11-000200 <span style="color:#f00;">Availability: 8/2012</span>

    ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 <span style="color:#f00;">Availability: 6/22/2012</span>

    3mmL Healing Collar Product code: 1037-13 <span style="color:#f00;">Availability: 6/27/2012</span>

    GPS™ Abutment Processing Kit-Single Product Code: 1000-98S <span style="color:#f00;">Availability: 6/28/2012</span>

    Is it possible to get the date value from the long string above? So, basically, I want to present the data in Excel with the product name (exactly shown above) and the next column will contain the date (date will be fetched from the product name)?

    I currently rely on Excel features to achieve this. However, I was hoping to get this done by SQL statements.

    Thank you,

    Fawad Rashidi
    www.fawadafr.com

  • Use the SUBSTRING() function and CHARINDEX() function.

    Syntax: SUBSTRING(<Expression>, <Start position>, <Length of string>)

    Expression is the string.

    Start position = use Charindex to identify the point where to start the substring

    Length of string = Use charindex to identify the point where the substring ends and then subtract the point where it begain (Start position)

    Declare @data Table (String varchar(250))

    Insert Into @data

    Values ('UMA Titanium Temporary Hex Cylinder Product Code: 11-000300 <span style="color:#f00;">Availability: 6/15/2012</span>'),

    ('UMA Hex Cylinder Product Code: 11-000200 <span style="color:#f00;">Availability: 8/2012</span>'),

    ('ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 <span style="color:#f00;">Availability: 6/22/2012</span>'),

    ('3mmL Healing Collar Product code: 1037-13 <span style="color:#f00;">Availability: 6/27/2012</span>'),

    ('GPS™ Abutment Processing Kit-Single Product Code: 1000-98S <span style="color:#f00;">Availability: 6/28/2012</span>')

    Select String,

    theDate = SUBSTRING(String,

    CHARINDEX('Availability: ', String) + LEN('Availability: '),

    CHARINDEX('</span>', String, CHARINDEX('Availability: ', String)) - (CHARINDEX('Availability: ', String) + LEN('Availability: ')))

    From @data


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Robert Davis (6/25/2012)


    Use the SUBSTRING() function and CHARINDEX() function.

    Syntax: SUBSTRING(<Expression>, <Start position>, <Length of string>)

    Expression is the string.

    Start position = use Charindex to identify the point where to start the substring

    Length of string = Use charindex to identify the point where the substring ends and then subtract the point where it begain (Start position)

    Declare @data Table (String varchar(250))

    Insert Into @data

    Values ('UMA Titanium Temporary Hex Cylinder Product Code: 11-000300 <span style="color:#f00;">Availability: 6/15/2012</span>'),

    ('UMA Hex Cylinder Product Code: 11-000200 <span style="color:#f00;">Availability: 8/2012</span>'),

    ('ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 <span style="color:#f00;">Availability: 6/22/2012</span>'),

    ('3mmL Healing Collar Product code: 1037-13 <span style="color:#f00;">Availability: 6/27/2012</span>'),

    ('GPS™ Abutment Processing Kit-Single Product Code: 1000-98S <span style="color:#f00;">Availability: 6/28/2012</span>')

    Select String,

    theDate = SUBSTRING(String,

    CHARINDEX('Availability: ', String) + LEN('Availability: '),

    CHARINDEX('</span>', String, CHARINDEX('Availability: ', String)) - (CHARINDEX('Availability: ', String) + LEN('Availability: ')))

    From @data

    It should work.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • fawadafr (6/25/2012)


    Please review the following examples of product names:

    UMA Titanium Temporary Hex Cylinder Product Code: 11-000300 <span style="color:#f00;">Availability: 6/15/2012</span>

    UMA Hex Cylinder Product Code: 11-000200 <span style="color:#f00;">Availability: 8/2012</span>

    ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 <span style="color:#f00;">Availability: 6/22/2012</span>

    3mmL Healing Collar Product code: 1037-13 <span style="color:#f00;">Availability: 6/27/2012</span>

    GPS™ Abutment Processing Kit-Single Product Code: 1000-98S <span style="color:#f00;">Availability: 6/28/2012</span>

    Is it possible to get the date value from the long string above? So, basically, I want to present the data in Excel with the product name (exactly shown above) and the next column will contain the date (date will be fetched from the product name)?

    I currently rely on Excel features to achieve this. However, I was hoping to get this done by SQL statements.

    Thank you,

    Hi Fawadafr,

    Robert has provided you the solution and should work for said examples.However I would like to ask few questions.

    1. Is the pattern of product string same in every case ?

    2. Is there a possibility of more than one date values in product strings ?

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • Hi bhuvneshk and Robert:

    All product names end with a date string (e.g. <span style="color:#f00;">Availability: 6/27/2012</span>)

    No, each product name string can only contain one date.

    Is it possible to get the date without creating any temp table?

    Fawad Rashidi
    www.fawadafr.com

  • The table variable was just to deo the code. Where do you have the data stored?


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • fawadafr (6/25/2012)


    Hi bhuvneshk and Robert:

    All product names end with a date string (e.g. <span style="color:#f00;">Availability: 6/27/2012</span>)

    No, each product name string can only contain one date.

    Is it possible to get the date without creating any temp table?

    So if the pattern is fixed and you have only one date value.We can do it in a simpler way as mentioned below.

    SELECT ProductName,SUBSTRING ( ProductName ,LEN(ProductName)-16 , 10) AS date

    FROM TableName

    Note - ProductName would be the column of your table.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • ;WITH SampleData AS (

    SELECT InputString = 'UMA Titanium Temporary Hex Cylinder Product Code: 11-000300 <span style="color:#f00;">Availability: 6/15/2012</span>' UNION ALL

    SELECT 'UMA Hex Cylinder Product Code: 11-000200 <span style="color:#f00;">Availability: 8/2012</span>' UNION ALL

    SELECT 'ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 <span style="color:#f00;">Availability: 6/22/2012</span>' UNION ALL

    SELECT '3mmL Healing Collar Product code: 1037-13 <span style="color:#f00;">Availability:6/27/2012</span>' UNION ALL

    SELECT 'GPS™ Abutment Processing Kit-Single Product Code: 1000-98S <span style="color:#f00;">Availability: 6/28/2012</span>'

    )

    SELECT

    InputString,

    Availability = RTRIM(LTRIM(SUBSTRING(InputString,startpos,endpos-startpos)))

    FROM SampleData

    CROSS APPLY (

    SELECT

    startpos = 13+CHARINDEX('Availability:',InputString,1),

    endpos = CHARINDEX('</span>',InputString,1)

    ) x

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • If you want Availability to end up as a DATE datatype, the trouble is obviously with the date that is missing the day. You can handle that like this (by transforming the ill-formed XML into something SQL can work with):

    ;WITH SampleData AS (

    SELECT InputString = 'UMA Titanium Temporary Hex Cylinder Product Code: 11-000300 <span style="color:#f00;">Availability: 6/15/2012</span>' UNION ALL

    SELECT 'UMA Hex Cylinder Product Code: 11-000200 <span style="color:#f00;">Availability: 8/2012</span>' UNION ALL

    SELECT 'ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 <span style="color:#f00;">Availability: 6/22/2012</span>' UNION ALL

    SELECT '3mmL Healing Collar Product code: 1037-13 <span style="color:#f00;">Availability:6/27/2012</span>' UNION ALL

    SELECT 'GPS™ Abutment Processing Kit-Single Product Code: 1000-98S <span style="color:#f00;">Availability: 6/28/2012</span>'

    )

    SELECT InputString

    ,Availability=CAST(Availability AS DATE)

    FROM SampleData

    CROSS APPLY (SELECT REPLACE(REPLACE(InputString, '">Availability:', '" Availability="'), '</span>', '" />')) x(input)

    CROSS APPLY (SELECT CAST(SUBSTRING(input, CHARINDEX('<span', input), LEN(input)) AS XML)) y(input2)

    CROSS APPLY (SELECT LTRIM(av.value('@Availability', 'VARCHAR(10)')) FROM input2.nodes('span') i(av)) z(Avail)

    CROSS APPLY (

    SELECT CASE LEN(Avail) - LEN(REPLACE(Avail, '/', ''))

    WHEN 0 THEN '01/01/' + Avail

    WHEN 1 THEN STUFF(Avail, CHARINDEX('/', Avail), 1, '/01/')

    WHEN 2 THEN Avail END) a(Availability)


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Chris:

    In your examples you are using the five product names I had provided. What would be the best way if I am dealing with 5,000+ products?

    Fawad Rashidi
    www.fawadafr.com

  • My expectation was that Chris's query would be faster. Shall we check?

    Test harness (5000 rows):

    SET STATISTICS TIME ON

    ;WITH Tally AS (

    SELECT TOP 1000 n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2),

    SampleData AS (

    SELECT InputString

    FROM (

    SELECT InputString = 'UMA Titanium Temporary Hex Cylinder Product Code: 11-000300 <span style="color:#f00;">Availability: 6/15/2012</span>' UNION ALL

    SELECT 'UMA Hex Cylinder Product Code: 11-000200 <span style="color:#f00;">Availability: 8/2012</span>' UNION ALL

    SELECT 'ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 <span style="color:#f00;">Availability: 6/22/2012</span>' UNION ALL

    SELECT '3mmL Healing Collar Product code: 1037-13 <span style="color:#f00;">Availability:6/27/2012</span>' UNION ALL

    SELECT 'GPS™ Abutment Processing Kit-Single Product Code: 1000-98S <span style="color:#f00;">Availability: 6/28/2012</span>'

    ) x

    CROSS APPLY Tally

    )

    SELECT

    InputString,

    Availability = RTRIM(LTRIM(SUBSTRING(InputString,startpos,endpos-startpos)))

    FROM SampleData

    CROSS APPLY (

    SELECT

    startpos = 13+CHARINDEX('Availability:',InputString,1),

    endpos = CHARINDEX('</span>',InputString,1)

    ) x

    ;WITH Tally AS (

    SELECT TOP 1000 n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2),

    SampleData AS (

    SELECT InputString

    FROM (

    SELECT InputString = 'UMA Titanium Temporary Hex Cylinder Product Code: 11-000300 <span style="color:#f00;">Availability: 6/15/2012</span>' UNION ALL

    SELECT 'UMA Hex Cylinder Product Code: 11-000200 <span style="color:#f00;">Availability: 8/2012</span>' UNION ALL

    SELECT 'ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 <span style="color:#f00;">Availability: 6/22/2012</span>' UNION ALL

    SELECT '3mmL Healing Collar Product code: 1037-13 <span style="color:#f00;">Availability:6/27/2012</span>' UNION ALL

    SELECT 'GPS™ Abutment Processing Kit-Single Product Code: 1000-98S <span style="color:#f00;">Availability: 6/28/2012</span>'

    ) x

    CROSS APPLY Tally

    )

    SELECT InputString

    ,Availability=CAST(Availability AS DATE)

    FROM SampleData

    CROSS APPLY (SELECT REPLACE(REPLACE(InputString, '">Availability:', '" Availability="'), '</span>', '" />')) x(input)

    CROSS APPLY (SELECT CAST(SUBSTRING(input, CHARINDEX('<span', input), LEN(input)) AS XML)) y(input2)

    CROSS APPLY (SELECT LTRIM(av.value('@Availability', 'VARCHAR(10)')) FROM input2.nodes('span') i(av)) z(Avail)

    CROSS APPLY (

    SELECT CASE LEN(Avail) - LEN(REPLACE(Avail, '/', ''))

    WHEN 0 THEN '01/01/' + Avail

    WHEN 1 THEN STUFF(Avail, CHARINDEX('/', Avail), 1, '/01/')

    WHEN 2 THEN Avail END) a(Availability)

    SET STATISTICS TIME OFF

    Results (Chris's is first):

    (5000 row(s) affected)

    SQL Server Execution Times:

    CPU time = 47 ms, elapsed time = 220 ms.

    (5000 row(s) affected)

    SQL Server Execution Times:

    CPU time = 795 ms, elapsed time = 1152 ms.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Wain:

    How about running Chris' script against the entire product table with over 5,000 products. So, the final data grid should include the product code, product name, and availability date (extracted from the product name.) How can I achieve this?

    Fawad Rashidi
    www.fawadafr.com

  • Chris has parsed the availability date only for you.

    You'd need to apply the CHARINDEX steps to parse out the other fields you need from the InputString in a similar fashion.

    His approach should still run pretty fast, even with all that parsing, with only 5000 rows.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Wain:

    How would I specify the 5K products in the SELECT statement and then UNION ALL after each product name following Chris' example?

    Fawad Rashidi
    www.fawadafr.com

  • I am not sure why you're thinking UNION ALL.

    All you need to do is replace SampleData after FROM in Chris's query with your table name (and get rid of his SampleData CTE). You'd also need to change the field name he's parting (InputString) into the column name from your table.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply