SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 String manipulation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ph1long
Starting Member

16 Posts

Posted - 10/16/2012 :  11:24:39  Show Profile  Reply with Quote
Hi,

I have a table called filesnames with 5 colums: FullDate, DistID, Month, Year, and FileName. I first fill the Filename column with files that are in this format AAA_2012-08_Warr.xls or AAA_2012-08_Stmt.pdf. I am trying to extract strings from these filenames to fill the last remaining 4 colums. For example:

Taking file AAA_2012-08_Warr.xls. I would like to extract and fill the columns like below:

FuLLDate: August 2012
DistID: AAA
Month: 08
Year: 2012

Please help.

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 10/16/2012 :  11:42:23  Show Profile  Reply with Quote
If the filename never deviates from your sample, this will work

DECLARE @str varchar(20)= 'AAA_2012-08_Warr.xls'

SELECT
PARSENAME(REPLACE(@str,'_','.'),4)as DistID
,RIGHT(PARSENAME(REPLACE(@str,'_','.'),3),2) as [Month]
,LEFT(PARSENAME(REPLACE(@str,'_','.'),3),4) as [Year]
You can derive FullDate from above.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000