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/
Go to Top of Page

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/

Go to Top of Page

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/
Go to Top of Page

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/

Go to Top of Page

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/
Go to Top of Page

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
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-21 : 16:14:27


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-21 : 16:33:56
[code]UPDATE dbo.Table
SET Col1 = SUBSTRING(Col1, 6, 8000)
WHERE Col1 LIKE '/123/%';[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-21 : 16:43:27
Isn't /123 just an example though?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -