Split string and replace

Question

I have a varchar column with Url with data that looks like this:

 http://google.mews.......http://www.somesite.com 

I want to get rid of the first http and save the second, so the line above will result in:

 http://www.somesite.com 

I tried using split() but cant get it working

thanks

Answer 1

If you are trying to do this using T-SQL, you can try something in the following lines:

 -- assume @v is the variable holding the URL SELECT SUBSTRING(@v, PATINDEX('%_http://%', @v) + 1, LEN(@v)) 

This will return the starting position of the first http: //, which has at least one character in front of it (hence the "% _" in front of it and the offset + 1).

Answer 2

If the first URL always starts at the beginning of the line, you can use SUBSTRING() and CHARINDEX() :

 SELECT SUBSTRING(column, CHARINDEX('http://', column, 2), LEN(column)) FROM table 

CHARINDEX just looks for the string for the substring and returns the starting position of the substring inside the string. Its third argument is optional and, if set, indicates the starting position of the search, in this case it is 2 , so it did not hit the first http:// .