Split string with delimiter in sql server

Question

I'm having a coloumn name with varchar field that holds some folder path like "C:\Program Files\Internet Explorer\en-US" .I need to update the root folder name(Program files to profilesNew).Can anyone please help. I tried with a query

declare @val as varchar(100)
set @val='C:\Program Files\Internet Explorer\en-US'

select substring(@val,charindex(':\',@val),charindex('\',@val))

but not getting the exact answer

C:\Program FilesNew\Internet Explorer\en-US

Answer 1

Why not use Replace instead?

declare @val as varchar(100) set @val='C:\Program Files\Internet Explorer\en-US'
Select Replace(@Val, 'Program Files', 'Program FilesNew')

Here's a more elaborate and entertaining version that asks the question. What if you don't know the base path before hand.

Create function fxUpdateBasePath(@Path as varchar(1000), @NewFolder as varchar(100))
returns varchar(1000) as
Begin
    Declare @NewPath as varchar(1000)
    Declare @drive as varchar(10)
    set @Drive = substring(@Path,charindex(':\',@Path) -1,charindex('\',@Path))
    Declare @Root as varchar(100)
    Set @Path = Substring(@Path, len(@Drive) +1, Len(@Path) - Len(@Drive))
    set @Root = Substring(@Path, 1, charindex('\', @Path) -1)
    set @Path = SubString(@Path, Len(@Root) + 1,Len(@Path) - Len(@Root) +1 )
    Set @NewPath = @Drive + @NewFolder + @Path
    return @NewPath
End

Go

    Select dbo.fxUpdateBasePath('C:\Program Files\Internet Explorer\en-US', 'Program FilesNew')
C:\Program FilesNew\Internet Explorer\en-US
    Select dbo.fxUpdateBasePath('C:\Program Files(x86)\Internet Explorer\en-US', 'Program FilesNew')
C:\Program FilesNew\Internet Explorer\en-US

Answer 2

you could always use a REPLACE instead of splitting and rejoining, if all you want to do is replace '\Program Files\' with '\profilesNew\'

UPDATE table SET column = REPLACE(column, '\Program Files\', '\profilesNew\');

Answer 3

Are you simply trying to change "Program Files" to "ProfilesNew"? If so, the following will do this:

DECLARE @val VARCHAR(100);
SET @val = 'C:\Program Files\Internet Explorer\en-US';

SELECT REPLACE(@val, 'C:\Program Files', 'C:\ProfilesNew');

gives you: C:\ProfilesNew\Internet Explorer\en-US

Answer 4

enter code here

declare @val as varchar(100)

set @val='C:\Program Files\Internet Explorer\en-US'

select @val

select replace(@val,Program Files',Program FilesNew')

Answer 5

Try this:

declare @val as varchar(100) 
declare @firstSlash int
declare @secondSlash int

set @val='C:\Program Files\Internet Explorer\en-US'

set @firstSlash = charindex('\',@val)
set @secondSlash = @firstSlash + charindex('\', substring(@val,@firstSlash+1,100))

select substring(@val, 1, @secondSlash-1) + 'New' + substring(@val, @secondSlash, 100)

Answer 6

try this for working on a set of paths:

declare @val table (val varchar(100))
INSERT @val VALUES ('C:\Program Files\Internet Explorer\en-US')
INSERT @val VALUES ('C:\My Documents\Internet Explorer\en-US')

SELECT 
    val,LEFT(val,CHARINDEX('\',val,CHARINDEX(':\',val)+3)-1)+'New\'+RIGHT(val,LEN(val)-CHARINDEX('\',val,CHARINDEX(':\',val)+3)) AS New
    FROM @Val

OUTPUT:

val                                       New
----------------------------------------- --------------------------------------------
C:\Program Files\Internet Explorer\en-US  C:\Program FilesNew\Internet Explorer\en-US
C:\My Documents\Internet Explorer\en-US   C:\My DocumentsNew\Internet Explorer\en-US

and this for a single variable:

declare @val as varchar(100)
set @val='C:\Program Files\Internet Explorer\en-US'

select @val
SELECT LEFT(@val,CHARINDEX('\',@val,CHARINDEX(':\',@val)+3)-1)+'New\'+RIGHT(@Val,LEN(@Val)-CHARINDEX('\',@val,CHARINDEX(':\',@val)+3))

OUTPUT:

-------------------------------------------
C:\Program Files\Internet Explorer\en-US


-------------------------------------------
C:\Program FilesNew\Internet Explorer\en-US