T-SQL: Get Path Value Into Separate Columns

declare @table_data table(f_Id int,FileName varchar(100) )
insert into @table_data
select 1,’C:\Docs\654\WD-1\HMREO_ACUBE_ASI_JEP_20120928.txt’
union all
select 2, ‘C:\Docs2\test\654\WD-1\HMREO_ACUBE_ASI_JEP_20120928.txt’ \

;with mycte as
(select f_id, Replace(replace(Stuff(FileName ,1,charindex(‘_’,FileName ),”),’_’,’.’),’.txt’,”) as FileName from
SELECT f_id, PARSENAME(filename ,4) as col4,
PARSENAME(filename ,3) as col3,
PARSENAME(filename ,2) as col2,
PARSENAME(filename ,1) as col1
from mycte

–Option2: XML
d.id.value(‘(/H/r)[2]’, ‘NVARCHAR(50)’) col2,
d.id.value(‘(/H/r)[3]’, ‘NVARCHAR(50)’) col3,
d.id.value(‘(/H/r)[4]’, ‘NVARCHAR(50)’) col4,
Replace(d.id.value(‘(/H/r)[5]’, ‘NVARCHAR(50)’),’.txt’,”) as col5

FROM (SELECT f_id, CAST (N” + REPLACE(FileName, ‘_’, ”) + ” as XML ) as id
FROM @table_data ) d


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s