Parse out file path

I have a table that has 6 columns, with only the media_path column currently populated

media_path
media_name_media_ext
media_name
dir_1
dir_2
dir_3
dir_4
dir_5

I would like to take the full path name located in the media_path column and insert the parsed portions into the other columns...like the following example:

H:\MEDIA_FILES\GLASS_REFLECTIVE_ICONS\BMP\128\glass_toggle_up_128.bmp

so for the full path above

media_name_media_ext = .bmp
media_name = glass_toggle_up_128
dir_1 = H:
dir_2 = MEDIA_FILES
dir_3 = GLASS_REFLECTIVE_ICONS
dir_4 = BMP
dir_5 = 128

there will always be 5 layers or less(dir1-5)

any help would greatly be appreciated!
LVL 1
H-SCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel_PLDB Expert/ArchitectCommented:
Assuming  
- values can be NULL,
- filed sizes

You can use something like this (you need to adapt to your enviroment):
 
-- create test table
CREATE TABLE #test (
media_path VARCHAR(1000),
media_name_media_ext VARCHAR(100),
media_name VARCHAR(100),
dir_1 VARCHAR(100),
dir_2 VARCHAR(100),
dir_3 VARCHAR(100),
dir_4 VARCHAR(100),
dir_5 VARCHAR(100))

-- populate table (only one value)
INSERT INTO #test (media_path) VALUES ('H:\MEDIA_FILES\GLASS_REFLECTIVE_ICONS\BMP\128\glass_toggle_up_128.bmp')

-- create procedure with @media_path parameter
CREATE PROCEDURE usp_update_dirs
@media_path VARCHAR(1000)
AS
BEGIN

DECLARE @media_path_orig VARCHAR(1000)
SET @media_path_orig=@media_path

DECLARE @data VARCHAR(1000)
DECLARE @media_name_media_ext VARCHAR(100)
DECLARE @media_name VARCHAR(100)
DECLARE @count TINYINT
DECLARE @sql NVARCHAR(2000)

SET @count=0

SET @media_name_media_ext=REPLACE(@media_path,SUBSTRING(@media_path,0,PATINDEX('%[.]%',@media_path)),'')
SET @sql='UPDATE #test SET media_name_media_ext='''+@media_name_media_ext+''','

WHILE PATINDEX('%[\]%',@media_path) > 0
BEGIN
IF @count=0
BEGIN
SET @data=SUBSTRING(@media_path,0,PATINDEX('%[\]%',@media_path))
SET @sql=@sql+'media_name='''+@data+''','
SET @media_path = REPLACE(@media_path,SUBSTRING(@media_path,1,PATINDEX('%[\]%',@media_path)),'')
END
SET @data=SUBSTRING(@media_path,0,PATINDEX('%[\]%',@media_path))
SET @media_path = REPLACE(@media_path,SUBSTRING(@media_path,1,PATINDEX('%[\]%',@media_path)),'')
SET @sql=@sql+'dir_'+CONVERT(CHAR(1),@count+1)+'='''+@data+''','
SET @count=@count+1
END
SELECT @sql=SUBSTRING(@sql,0,LEN(@sql))+' WHERE media_path='''+@media_path_orig+''''
EXECUTE sp_executesql @sql
END

-- update table with proper values, note that not existing dirs will not be updated
EXEC usp_update_dirs @media_path='H:\MEDIA_FILES\GLASS_REFLECTIVE_ICONS\BMP\128\glass_toggle_up_128.bmp'

-- see what was updated
SELECT * from #test

-- drop test table
DROP TABLE #test

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
H-SCAuthor Commented:
Daniel_PL,

This is awesome work!  It works perfect!  Many thank you's for your much needed help on this one.
0
Daniel_PLDB Expert/ArchitectCommented:
That's great, I'm glad you're satisfied ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.