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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
38200 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
38200 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
38200 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
38200 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
30421 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
38200 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  
 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.06 seconds. Powered By: Snitz Forums 2000