September 27, 2011 at 3:12 pm
Hello All:
In the Products table there is a field for ProductName where I also add the Availability dates so they print on the customers' invoices. I run daily reports to stay on top of the availability dates for each product. In order to accomplish this, I run a script as follow:
SELECT ProductCode
,ProductName
,HideProduct
FROM Products
WHERE ProductName LIKE '%Availability%'
GO
Here are some sample results:
Some Product Name Here 3.7mmD X 8mmL (SBM)
Product Code: 103708
<span style="color:red;">Availability: 9/26/2011</span>
Some Product Name Here 3.7mmD X 10mmL (SBM)
Product Code: 103710
<span style="color:red;">Product Code: 103708
Availability: 9/26/2011</span>
If you noticed, the date is wrapped with <SPAN> HTML tag for special styling.
I then copy-and-paste the data in Excel and use Excel tools (such as Find-and-Replace and Text-to-Column) to get the dates from the product name field.
Ideally, I would like another column (e.g. Availability) where I get the date for each part number from its name and even do the sorting so I can easily see which products are outdated or closer to becoming outdated.
Thank you,
Fawad Rashidi
www.fawadafr.com
September 28, 2011 at 3:57 am
Hi,
You can use Substring function here.
In which field you are storing date ?
Make it clear i will try to write query.
Shatrughna
September 28, 2011 at 4:07 am
Your sample data is a bit confusing.
Looks like you have a field in the format
<span [text] MM/DD/YYYY</span>
is that correct?
If it is, would it be fair to say that extracting the 10 characters immediately preceding </span> would always give the date?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 28, 2011 at 4:15 am
--Some test data first
DECLARE @TABLE AS TABLE (tableText VARCHAR(1000))
INSERT INTO @TABLE
SELECT 'Some Product Name Here 3.7mmD X 8mmL (SBM)
Product Code: 103708
<span style="color:red;">Availability: 9/26/2011</span>'
UNION ALL SELECT 'Some Product Name Here 3.7mmD X 10mmL (SBM)
Product Code: 103710
<span style="color:red;">Product Code: 103708
Availability: 9/26/2011</span>'
--Query
SELECT RTRIM(LTRIM(Substring(deliveryText, start, length - start - fromEnd + 2))) AS [extracted]
FROM (
SELECT MAX(PATINDEX([Matched], tableText)) AS start
,MAX(PATINDEX(ReverseMatch, reverse(tableText + ' ')) - 1) AS fromEnd
,len(tableText + '|') - 1 AS [length]
,tableText AS deliveryText
FROM (
SELECT '%[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]</span>%'
,'%<naps/>[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9][0-1]%'
UNION ALL
SELECT '%[0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]%'
,'%[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9]%'
) AS f([Matched], ReverseMatch)
CROSS JOIN @TABLE
GROUP BY tableText
) search
September 28, 2011 at 1:41 pm
Sure, I have tired using the SUBSTRING function but had no luck. I would like to sort the fields where I display the extracted dates from the product name field.
Thank you,
shatrughna (9/28/2011)
Hi,You can use Substring function here.
In which field you are storing date ?
Make it clear i will try to write query.
Fawad Rashidi
www.fawadafr.com
September 28, 2011 at 1:45 pm
@Phil:
Not all the records for the product name have the same exact length or format. However, all the availability dates have this exact format:
[Product Name Here] <span style="color:red;">Availability: 9/23/2011</span>
Thank you,
Phil Parkin (9/28/2011)
Your sample data is a bit confusing.Looks like you have a field in the format
<span [text] MM/DD/YYYY</span>is that correct?
If it is, would it be fair to say that extracting the 10 characters immediately preceding </span> would always give the date?
Fawad Rashidi
www.fawadafr.com
September 28, 2011 at 1:47 pm
Cadavre (9/28/2011)
--Some test data firstDECLARE @TABLE AS TABLE (tableText VARCHAR(1000))
INSERT INTO @TABLE
SELECT 'Some Product Name Here 3.7mmD X 8mmL (SBM)
Product Code: 103708
<span style="color:red;">Availability: 9/26/2011</span>'
UNION ALL SELECT 'Some Product Name Here 3.7mmD X 10mmL (SBM)
Product Code: 103710
<span style="color:red;">Product Code: 103708
Availability: 9/26/2011</span>'
--Query
SELECT RTRIM(LTRIM(Substring(deliveryText, start, length - start - fromEnd + 2))) AS [extracted]
FROM (
SELECT MAX(PATINDEX([Matched], tableText)) AS start
,MAX(PATINDEX(ReverseMatch, reverse(tableText + ' ')) - 1) AS fromEnd
,len(tableText + '|') - 1 AS [length]
,tableText AS deliveryText
FROM (
SELECT '%[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]</span>%'
,'%<naps/>[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9][0-1]%'
UNION ALL
SELECT '%[0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]%'
,'%[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9]%'
) AS f([Matched], ReverseMatch)
CROSS JOIN @TABLE
GROUP BY tableText
) search
Nice solution
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2011 at 2:02 pm
Great solution as it covers all the possibilities in the product name field. However:
1) Is it possible to add this code in the following SQL statement so the new field (extracted) shows up next to its corresponding product code?
2) The date format needs to be adjusted since the month gets truncated for the two digit months (e.g. for month October I only see '0' for the month.)
SELECT [ProductCode]
,[ProductName]
--, extracted field here...
FROM [Products]
WHERE ProductName LIKE '%Availability%'
GO
Thank you,
Fawad Rashidi
www.fawadafr.com
September 28, 2011 at 2:10 pm
Try this updated script:
SELECT tabletext,RTRIM(LTRIM(Substring(deliveryText, start -1, length - start - fromEnd + 3))) AS [extracted]
FROM (
SELECT tabletext,MAX(PATINDEX([Matched], tableText)) AS start
,MAX(PATINDEX(ReverseMatch, reverse(tableText + ' ')) - 1) AS fromEnd
,len(tableText + '|') - 1 AS [length]
,tableText AS deliveryText
FROM (
SELECT '%[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]</span>%'
,'%<naps/>[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9][0-1]%'
UNION ALL
SELECT '%[0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]%'
,'%[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9]%'
) AS f([Matched], ReverseMatch)
CROSS JOIN @TABLE
GROUP BY tableText
) search
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2011 at 2:15 pm
This worked great. Thank you very much!
Question: why do I have to declare the TABLE, INSERT data, etc. Would it be possible to use only one SELECT statement and get the dates?
Fawad Rashidi
www.fawadafr.com
September 28, 2011 at 2:17 pm
The only reason for that table variable was due to the need to create a testable script.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2011 at 2:21 pm
I see. Now that we know the script works fine and there is no need for the testing, would it be possible incorporate the script to work with only one SELECT statement by getting the data from the Products table directly instead?
Thank you,
Fawad Rashidi
www.fawadafr.com
September 28, 2011 at 2:25 pm
Yes - you can adapt this script to your actual table. However, you will still need the two subqueries inside that single select statement that has been provided.
Just change the @Table and the column names in order to incorporate it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 28, 2011 at 2:46 pm
Yes, I made the following change to the code provided. However, how would I add the product code for each date?
--Some test data first
DECLARE @TABLE AS TABLE (tableText VARCHAR(1000))
INSERT INTO @TABLE
SELECT ProductName FROM Products WHERE ProductName LIKE '%Availability%'
UNION ALL SELECT ProductName FROM Products WHERE ProductName LIKE '%Availability%'
--Query
SELECT tabletext,RTRIM(LTRIM(Substring(deliveryText, start -1, length - start - fromEnd + 3))) AS [extracted]
FROM (
SELECT tabletext,MAX(PATINDEX([Matched], tableText)) AS start
,MAX(PATINDEX(ReverseMatch, reverse(tableText + ' ')) - 1) AS fromEnd
,len(tableText + '|') - 1 AS [length]
,tableText AS deliveryText
FROM (
SELECT '%[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]</span>%'
,'%<naps/>[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9][0-1]%'
UNION ALL
SELECT '%[0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]%'
,'%[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9]%'
) AS f([Matched], ReverseMatch)
CROSS JOIN @TABLE
GROUP BY tableText
) search
Fawad Rashidi
www.fawadafr.com
September 28, 2011 at 2:48 pm
Please provide your table structure (table create script).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply