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

Question

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

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

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

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

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
🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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 Availability: 6/15/2012' UNION ALL

SELECT 'UMA Hex Cylinder Product Code: 11-000200 Availability: 8/2012' UNION ALL

SELECT 'ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 Availability: 6/22/2012' UNION ALL

SELECT '3mmL Healing Collar Product code: 1037-13 Availability:6/27/2012' UNION ALL

SELECT 'GPS™ Abutment Processing Kit-Single Product Code: 1000-98S Availability: 6/28/2012'

)

SELECT

InputString,

Availability = CONVERT(DATETIME,y.StrAvailability,101)

FROM SampleData

CROSS APPLY (

SELECT

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

endpos = CHARINDEX('',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

fawadafr SSC Eights! Points: 865 More actions · Answer 3

I came up with an easier way:

Check this out:

select productcode,

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

from products

Fawad Rashidi
www.fawadafr.com

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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),'','')) 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