June 26, 2012 at 10:54 pm
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
🙂
June 27, 2012 at 1:52 am
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
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
July 5, 2012 at 12:41 pm
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
July 6, 2012 at 1:56 am
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.
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