SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Looking to replace text between two '/' forward sl
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shelbyoh
Starting Member

14 Posts

Posted - 08/19/2014 :  15:01:07  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 08/19/2014 :  15:09:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 08/21/2014 :  08:06:22  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 08/21/2014 :  12:35:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 08/21/2014 12:36:07
Go to Top of Page

shelbyoh
Starting Member

14 Posts

Posted - 08/21/2014 :  15:00:34  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 08/21/2014 :  15:06:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 08/21/2014 :  16:11:23  Show Profile  Reply with Quote

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

USA
37157 Posts

Posted - 08/21/2014 :  16:14:27  Show Profile  Visit tkizer's Homepage  Reply with Quote


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

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 08/21/2014 :  16:33:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE dbo.Table 
SET    Col1 = SUBSTRING(Col1, 6, 8000)
WHERE  Col1 LIKE '/123/%';



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

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 08/21/2014 :  16:43:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
Isn't /123 just an example though?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000