Get Date from String - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get Date from String

Author

Topic

fawadafr
Starting Member

47 Posts

Posted - 2011-09-26 : 11:49:27

Hi All:

Is it possible to get the date from string? Here is an example of the String:

Hello: this is the product name text Product Code: XXXX-XX Availability: 9/26/2011

So the result should be: 9/26/2011

Thank you,

--
Fawad Rashidi
Web Developer
www.fawadafr.com

Kristen
Test

22859 Posts

Posted - 2011-09-26 : 11:52:02

Is it always preceded by "Availability: "?
Is it always in American format mm/dd/yyyy (presuambly also can be m/d/yyyy, m/dd/yyyy and mm/d/yyyy)

Is there only ever one matching such date in the string?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 11:53:29

can there be more than one dates coming in same string?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fawadafr
Starting Member

47 Posts

Posted - 2011-09-26 : 11:56:06

@Kristen:

Yes, it always preceded by "Availability: ", American format, and the dates often changes.

Thank you

--
Fawad Rashidi
Web Developer
www.fawadafr.com

fawadafr
Starting Member

47 Posts

Posted - 2011-09-26 : 11:56:57

@Visakh16:

Yes, there can. Thank you.

--
Fawad Rashidi
Web Developer
www.fawadafr.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 12:02:36

in that case you want all those dates coming as a string or in different rows? Sample output for some sample data would help us to understand your exact scenario

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fawadafr
Starting Member

47 Posts

Posted - 2011-09-26 : 12:14:35

@Visakh16:

I would like to get the data as "date" format in a different column. Basically, the date value is embedded in the product_name column in the database. I would like to retrieve the date from the long string so I can easily calculate the availability for each product.

I currently use Excel "Text to Column" feature, I am trying to get this done within SQL to save an extra step.

Thank you,

--
Fawad Rashidi
Web Developer
www.fawadafr.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 12:19:43

and in all dates you've preceeding word Availability?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fawadafr
Starting Member

47 Posts

Posted - 2011-09-26 : 12:27:28

@Visakh16:

Yes, that is correct.

--
Fawad Rashidi
Web Developer
www.fawadafr.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-26 : 13:12:37

If the format is same

declare @s varchar(1000)
set @s='hello: this is the product name text product code: xxxx-xx availability: 9/26/2011'
select substring(@s,charindex('availability',@s)+13,10)

Madhivanan

Failing to plan is Planning to fail

fawadafr
Starting Member

47 Posts

Posted - 2011-09-26 : 13:30:43

@madhivanan,

The format is not exactly the same as the product name changes. Please see the sample results bellow:



ProductCode                    ProductName                                                                                                                                                                                                                                                     Test
------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
923308                         SwishPlus™ Implant 3.3mmD x 8mmL SBM: 3.7mm Platform<br>Product Code: 923308<br><font color="red">Availability: End of November</font>                                                                                                                          mplant 3.3
923310                         SwishPlus™ Implant 3.3mmD x 10mmL SBM: 3.7mm Platform<br>Product Code: 923310<br><font color="red">Availability: End of November</font>                                                                                                                         mplant 3.3
923312                         SwishPlus™ Implant 3.3mmD x 12mmL SBM: 3.7mm Platform<br>Product Code: 923312<br><font color="red">Availability: End of November</font>                                                                                                                         mplant 3.3
923314                         SwishPlus™ Implant 3.3mmD x 14mmL SBM: 3.7mm Platform<br>Product Code: 923314<br><font color="red">Availability: End of November</font>                                                                                                                         mplant 3.3
923316                         SwishPlus™ Implant 3.3mmD x 16mmL SBM: 3.7mm Platform<br>Product Code: 923316<br><font color="red">Availability: End of November</font>                                                                                                                         mplant 3.3
853706                         Legacy3 Implant: 3.7mmD x 6mmL SBM: 3.5mmD Platform<br/>Product Code: 853706<br><font color="red">Availability: 9/30/2011</font>                                                                                                                                ant: 3.7mm
854206                         Legacy3 Implant: 4.2mmD x 6mmL SBM: 3.5mmD Platform<br/>Product Code: 854206<br><font color="red">Availability: 9/30/2011</font>                                                                                                                                ant: 4.2mm
854706                         Legacy3 Implant: 4.7mmD x 6mmL SBM: 4.5mmD Platform<br/>Product Code: 854706<br><font color="red">Availability: 9/30/2011</font>                                                                                                                                ant: 4.7mm
855206                         Legacy3 Implant: 5.2mmD x 6mmL SBM: 4.5mmD Platform<br/>Product Code: 855206<br><font color="red">Availability: 9/30/2011</font>                                                                                                                                ant: 5.2mm
855706                         Legacy3 Implant: 5.7mmD x 6mmL SBM: 5.7mmD Platform<br/>Product Code: 855706<br><font color="red">Availability: 9/30/2011</font>                                                                                                                                ant: 5.7mm

(10 row(s) affected)

--
Fawad Rashidi
Web Developer
www.fawadafr.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-26 : 13:39:59

So date is always the last part in the data?

Madhivanan

Failing to plan is Planning to fail

fawadafr
Starting Member

47 Posts

Posted - 2011-09-26 : 13:43:56

@madhivanan:

Yes, it is. The date is always wrapped by the HTML code for special styling. So, it always end with tag.

Thank you,

--
Fawad Rashidi
Web Developer
www.fawadafr.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 14:37:53

[code]
Make a udf like this

CREATE FUNCTION ParseValues
(@String varchar(8000), @Delimiter varchar(1000) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(8000))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

then call it like

SELECT t.ProductCode,
,STUFF(f.val,PATINDEX('%%',f.Val),LEN(f.Val)-PATINDEX('%%',f.Val)+1,'')
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.ProductName,'Availability:') f
WHERE PATINDEX('%[0-9]/[0-9][0-9]/[1,2][0,9][0-9][0-9]%',f.Val)>0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fawadafr
Starting Member

47 Posts

Posted - 2011-09-26 : 16:16:05

@Visakh16,

Thank you very much for the help! When I run your script, I get the follow error message:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.

When I double click on the error message, this line in SQL code gets highlighted:

CROSS APPLY dbo.ParseValues(t.ProductName,'Availability:') f

Thank you,

--
Fawad Rashidi
Web Developer
www.fawadafr.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 00:39:29

are you using sql 2005 or above? is your compatibility level at least 90? if not, APPLY wont work. Run below query and post the result

SELECT @@VERSION
GO
EXEC sp_dbcmptlevel 'your db name'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fawadafr
Starting Member

47 Posts

Posted - 2011-09-27 : 11:17:29

@Visakh16:

Yes, I am. Here is the result:

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Thank you,

--
Fawad Rashidi
Web Developer
www.fawadafr.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 11:29:48

you didnt post us compatibility level still

EXEC sp_dbcmptlevel 'your db name'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fawadafr
Starting Member

47 Posts

Posted - 2011-09-27 : 11:40:43

The compatibility level is: 80.

--
Fawad Rashidi
Web Developer
www.fawadafr.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 11:51:35

thats the reason. unless you make it 90 you cant use APPLY operator

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fawadafr
Starting Member

47 Posts

Posted - 2011-09-27 : 12:04:09

How can I make it 90? Are there any workaround?

Thank you,

--
Fawad Rashidi
Web Developer
www.fawadafr.com

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources