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)
 String manipulation

Author  Topic 

ph1long
Starting Member

16 Posts

Posted - 2012-10-16 : 11:24:39
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-16 : 11:42:23
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
   

- Advertisement -