Looking to replace text between two '/' forward slashes

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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.

Declare @Url varchar(max)

SQL

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.

Posted 19-Aug-14 8:41am

postonoh

Updated 21-Aug-20 0:01am

Add a Solution

4 solutions

Solution 1

Why are you getting so confused brother?

Just a simple C# code would do that, when you access the data, try to get the data that you're trying to replace (entire string) in a seperate variable. Something like

C#

string toReplace = "/123/abc/987"; // we'll take string in variable
string[] replacedString = toReplace.Split('/');
// 1 indexer will get the second item in the above code, '123'
replacedString[1] = "Your name here...";
// above code would update that, convert it back to string and show it!

This would do it, you can execute the same on the second string too. Same code would work on both and will replace the second item (the one between /) and then add your custom name value that you want to pass.

Posted 19-Aug-14 8:50am

Afzaal Ahmad Zeeshan

Solution 2

Split the string in an array, substitute what you want and bond it again to a string...

C#

public void Replace1(string contents, out string output)
       {
           output = "";
           if (contents.Length > 0)
           {
               //Split the content in lines
               string[] Values = contents.Split(new char[] { '\\' });
               Values[1] = "132";
               for(int i = 0; i < Values.Length; i++)
               {
                   output +=Values[i]+"\\";
               }
           }
       }

Posted 19-Aug-14 8:51am

LucasRLS

Solution 4

Removes Text between two characters in a string

My code is in vb.net, but you can convert it to any language
Dim content as string ="The Quick Brown Fox @ REMOVE ME IF YOU CAN # jumped over a lazy dog

RemoveString(content)

Private Function RemoveString(content As String) As String
Dim SPos As Integer = content.IndexOf("@@")
Dim Subpos As Integer = content.IndexOf("##", SPos)
Dim Remstrlength As Integer = Subpos - SPos
content =content.Remove(SPos, Remstrlength)
content = content.Replace("@@", "")
content = content.Replace("##", "")
Return content
End Function

Output:"The Quick Brown Fox jumped over a lazy dog

Posted 21-Aug-20 0:01am

Vijay_Bhat

Comments

CHill60 21-Aug-20 8:38am

Off topic and 6 years late. Stick to answering new posts. And this doesn't really work unless the string contains "@@" and "##" - which your example doesn't

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Oshtri Deka · Accepted Answer · 2014-08-19T12:13:00

Solution 3

I am not sure if you are aware of it, but in SQL indexes start with 1 and not with 0 as in C and its successors.

try replacing this:

SQL

REPLACE(SUBSTRING(ReportLocation, CHARINDEX('/', ReportLocation), LEN(ReportLocation)), '/', 'newone') AS ReportLocation

with this:

SQL

'/' + 'new value' + SUBSTRING(ReportLocation, CHARINDEX(ReportLocation, '/', 2), LEN(ReportLocation)) AS ReportLocation

Posted 19-Aug-14 12:13pm

Oshtri Deka

Comments

postonoh 21-Aug-14 11:01am

ok so this works the replace, so one additional question if I want to remove starting at from the second forward slash example "/123/abc/987" should look like this afterwards "/abc/987

Oshtri Deka 21-Aug-14 11:53am

If I have understood you correctly, you just have to skip first two strings in concatenation ('/' + 'new value') which will leave you this:
SUBSTRING(ReportLocation, CHARINDEX(ReportLocation, '/', 2), LEN(ReportLocation)).

I hope this helps.