In my table Products in SQL Server, I have a column UrlLink with values that look like this:
Id UrlLink
-----------------------------------
1 domain/product1.html?7
2 domain/product2.html?34
3 domain/product294.html?6576
4 domain/product54.html?765
How to remove parameter
?7, ?34, ?6576, ?765
from column UrlLink?
Thanks!
Using left and charindex should work:
select left(UrlLink, charindex('?',UrlLink)-1) from Products;
This would return everything before the first occurrence of a ?. You might want to add some null checks if parameter isn't mandatory in the UrlLink column.
Try with this code:
DECLARE @myvar varchar(100);
SET @myvar = 'domain/product1.html?7';
SELECT REVERSE(SUBSTRING((REVERSE(@myvar)), (CHARINDEX('?',REVERSE(@myvar))+1),500)) AS result ;
GO
Urllink that does not contain?. ? removes everything from the first ? since in URL this is the only ? with special significance.select id,left(Urllink,charindex('?',Urllink+'?')-1)
from Products
You just have to find ? in your url and take string upto it. You can use LEFT/SUBSTRING for getting substring and CHARINDEX for finding ? in your string. Check out my query below
select id, substring(urllink,1,charindex('?',urllink)-1)
from products
In case multiple ? symbols are there in the UrlLink column values and if you want to take the value after the last ? symbol. Then use a combination of SUBSTRING and CHARINDEX .
Query
SELECT
[Id],
SUBSTRING([UrlLink], 1,
LEN([UrlLink]) - CHARINDEX('?', REVERSE([UrlLink]), 1)) AS [UrlLink]
FROM [Products];
