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

  • fawadafr (6/26/2012)


    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?

    Hey Fawad,

    Did you try out the query posted by me ?

    As you have a fixed pattern give a trial to that.One more thing you can add to that code trim the spaces from both side that should work in any product string.

    --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/26/2012)


    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?

    The best way, as always, is to test all the methods to establish which is the quickest (cheapest). Here's a modified method, which like Dwain's, returns a datetime:

    ;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 = CONVERT(DATETIME,y.StrAvailability,101)

    FROM SampleData

    CROSS APPLY (

    SELECT

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

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

    ) x

    CROSS APPLY (

    SELECT StrAvailability = CASE

    WHEN LEN(d.StrAvailability) < 8 THEN STUFF(d.StrAvailability,3,0,'01/')

    ELSE d.StrAvailability END

    FROM (SELECT StrAvailability = LTRIM(SUBSTRING(InputString,startpos,endpos-startpos))) d

    ) y

    “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

  • I came up with an easier way:

    Check this out:

    select productcode,

    (replace(substring(p2.productname,patindex('%Availability: %',p2.productname)+14,100),'</span>','')) AS Availability

    from products

    Fawad Rashidi
    www.fawadafr.com

  • fawadafr (7/5/2012)


    I came up with an easier way:

    Check this out:

    select productcode,

    (replace(substring(p2.productname,patindex('%Availability: %',p2.productname)+14,100),'</span>','')) AS Availability

    from products

    I did, and it fails.

    Test it against the sample data. Fix it, then add whatever is necessary to convert the output into a datetime.

    “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

Viewing 4 posts - 16 through 18 (of 18 total)

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