| Author |
 Topic  |
|
|
ph1long
Starting Member
16 Posts |
 Posted - 10/16/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 10/16/2012 : 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 |
 |
|
| |
Topic |
|
|
|
String manipulation
