June 25, 2012 at 8:41 pm
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
June 25, 2012 at 10:38 pm
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
June 25, 2012 at 11:11 pm
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
🙂
June 25, 2012 at 11:17 pm
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
🙂
June 25, 2012 at 11:28 pm
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
June 25, 2012 at 11:45 pm
The table variable was just to deo the code. Where do you have the data stored?
June 26, 2012 at 12:17 am
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
🙂
June 26, 2012 at 3:11 am
;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
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
June 26, 2012 at 7:22 pm
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 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
June 26, 2012 at 9:38 pm
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
June 26, 2012 at 9:52 pm
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 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
June 26, 2012 at 9:56 pm
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
June 26, 2012 at 10:10 pm
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 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
June 26, 2012 at 10:15 pm
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
June 26, 2012 at 10:27 pm
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 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