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?
 
Daniel_PLConnect With a Mentor DB 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.