CODEWITHSUNDEEP
sql-server
Renju
Renju

Reputation: 11

Split string with delimiter in sql server

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

Upvotes: 1

Views: 4640

Answers (6)

Renju
Renju

Reputation: 11

declare @val as varchar(100)

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

select @val

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

Upvotes: 0

cmsjr
cmsjr

Reputation: 59225

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

Upvotes: 0

KM.
KM.

Reputation: 103697

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

Upvotes: 1

user121301
user121301

Reputation:

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)

Upvotes: 1

Andy Shellam
Andy Shellam

Reputation: 15545

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

Upvotes: 0

chris
chris

Reputation: 10003

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\');

Upvotes: 0

Related Questions