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.
| 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 2012DistID: AAAMonth: 08Year: 2012Please 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 workDECLARE @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.JimEveryday I learn something that somebody else already knew |
 |
|
|
|
|
|
|
|
String manipulation