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

Question

Post reply

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

fawadafr

SSC Eights!

Points: 865
More actions
June 25, 2012 at 8:41 pm

#276844

Please review the following examples of product names:
UMA Titanium Temporary Hex Cylinder Product Code: 11-000300 Availability: 6/15/2012
UMA Hex Cylinder Product Code: 11-000200 Availability: 8/2012
ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 Availability: 6/22/2012
3mmL Healing Collar Product code: 1037-13 Availability: 6/27/2012
GPS™ Abutment Processing Kit-Single Product Code: 1000-98S Availability: 6/28/2012
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

Viewing 15 posts - 1 through 15 (of 18 total)

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

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 Availability: 6/15/2012'),

('UMA Hex Cylinder Product Code: 11-000200 Availability: 8/2012'),

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

('3mmL Healing Collar Product code: 1037-13 Availability: 6/27/2012'),

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

Select String,

theDate = SUBSTRING(String,

CHARINDEX('Availability: ', String) + LEN('Availability: '),

CHARINDEX('', String, CHARINDEX('Availability: ', String)) - (CHARINDEX('Availability: ', String) + LEN('Availability: ')))

From @data

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

rhythmk SSCertifiable Points: 7271 More actions · Answer 2

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 Availability: 6/15/2012'),
('UMA Hex Cylinder Product Code: 11-000200 Availability: 8/2012'),
('ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 Availability: 6/22/2012'),
('3mmL Healing Collar Product code: 1037-13 Availability: 6/27/2012'),
('GPS™ Abutment Processing Kit-Single Product Code: 1000-98S Availability: 6/28/2012')
Select String,
theDate = SUBSTRING(String,
CHARINDEX('Availability: ', String) + LEN('Availability: '),
CHARINDEX('', 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
🙂

rhythmk SSCertifiable Points: 7271 More actions · Answer 3

fawadafr (6/25/2012)
Please review the following examples of product names:
UMA Titanium Temporary Hex Cylinder Product Code: 11-000300 Availability: 6/15/2012
UMA Hex Cylinder Product Code: 11-000200 Availability: 8/2012
ScrewPlant® GPS™ Abutment: 3.7mmD Plat/2mmL Collar Height Product Code: 1037-92 Availability: 6/22/2012
3mmL Healing Collar Product code: 1037-13 Availability: 6/27/2012
GPS™ Abutment Processing Kit-Single Product Code: 1000-98S Availability: 6/28/2012
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
🙂

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

Hi bhuvneshk and Robert:

All product names end with a date string (e.g. Availability: 6/27/2012)

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

The table variable was just to deo the code. Where do you have the data stored?

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

rhythmk SSCertifiable Points: 7271 More actions · Answer 6

fawadafr (6/25/2012)
Hi bhuvneshk and Robert:
All product names end with a date string (e.g. Availability: 6/27/2012)
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
🙂

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

;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 = RTRIM(LTRIM(SUBSTRING(InputString,startpos,endpos-startpos)))

FROM SampleData

CROSS APPLY (

SELECT

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

endpos = CHARINDEX('',InputString,1)

) x

^{“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

Dwain Camps SSC Guru Points: 86938 More actions · Answer 8

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 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=CAST(Availability AS DATE)

FROM SampleData

CROSS APPLY (SELECT REPLACE(REPLACE(InputString, '">Availability:', '" Availability="'), '', '" />')) 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

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

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

Dwain Camps SSC Guru Points: 86938 More actions · Answer 10

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

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

) x

CROSS APPLY Tally

)

SELECT InputString

,Availability=CAST(Availability AS DATE)

FROM SampleData

CROSS APPLY (SELECT REPLACE(REPLACE(InputString, '">Availability:', '" Availability="'), '', '" />')) 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

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

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

Dwain Camps SSC Guru Points: 86938 More actions · Answer 12

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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

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

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

Dwain Camps SSC Guru Points: 86938 More actions · Answer 14

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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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