Looking to replace text between two '/' forward sl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Looking to replace text between two '/' forward sl

Author

Topic

shelbyoh
Starting Member

14 Posts

Posted - 2014-08-19 : 15:01:07

Looking to replace text between two '/' forward slashes
I trying to replace text between the first two forward slash "/" in my datacolumn.

I have the following which allows me to get everything before.

Select ReportLocation, substring(ReportLocation, 0, Len(ReportLocation)-charindex ('/', reverse(ReportLocation)) +2) from Report

Then I tried this which changes all the data in the column to the same.

SELECT ReportLocation
,LEFT(ReportLocation, CHARINDEX('/', ReportLocation) - 0) AS [Surname]
,REPLACE(SUBSTRING(ReportLocation, CHARINDEX('/', ReportLocation), LEN(ReportLocation)), '/', 'newone') AS ReportLocation
FROM Report
where CHARINDEX('/', ReportLocation) > 0

What I am looking for is some that can replace text between the first two '/'

example:

Column Name: ReportLocation

record 1 in Column: /123/abc/987

record 2 in Column: /xyz/123/456/478

In record 1 the following should happen record /123/abc/987. 123 should be replace with what name I add, record 2 /xyz/123/456, xyz should be hould be replace with what name I add

Thanks a head of time.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-19 : 15:09:10

Use your column/table instead of @s:
d eclare @s varchar(100), @replace varchar(100)
select @s = '/123/abc/987', @replace = 'replacewiththis'

select substring(@s, charindex('/', @s), 1) + @replace + substring(@s, charindex('/', @s, charindex('/', @s)+1), datalength(@s))

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

shelbyoh
Starting Member

14 Posts

Posted - 2014-08-21 : 08:06:22

Thanks that Worked.

I have one more request. my column inserts data like this /123/abc/987 Is there away to remove the following /123 everthing before the second forward slash.

quote:
Originally posted by tkizer

Use your column/table instead of @s:
d eclare @s varchar(100), @replace varchar(100)
select @s = '/123/abc/987', @replace = 'replacewiththis'

select substring(@s, charindex('/', @s), 1) + @replace + substring(@s, charindex('/', @s, charindex('/', @s)+1), datalength(@s))

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-21 : 12:35:50

quote:
Originally posted by shelbyoh

I have one more request. my column inserts data like this /123/abc/987 Is there away to remove the following /123 everthing before the second forward slash.

I don't understand. Could you show expected output for that sample?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

shelbyoh
Starting Member

14 Posts

Posted - 2014-08-21 : 15:00:34

Example data /123/abc/987
after data /abc/987

quote:
Originally posted by tkizer

quote:
Originally posted by shelbyoh

I have one more request. my column inserts data like this /123/abc/987 Is there away to remove the following /123 everthing before the second forward slash.

I don't understand. Could you show expected output for that sample?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-21 : 15:06:00

select substring(@s, charindex('/', @s) + charindex('/', substring(@s, charindex('/', @s)+1, datalength(@s))), datalength(@s))

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

shelbyoh
Starting Member

14 Posts

Posted - 2014-08-21 : 16:11:23

quote:
Originally posted by tkizer

select substring(@s, charindex('/', @s) + charindex('/', substring(@s, charindex('/', @s)+1, datalength(@s))), datalength(@s))

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Did the trick thanks