How to remove everything after last "/" in SQL Server column-CodePudding

I have a SQL SERVER column named full_url of type nvarchar(1000). Sample string look like a classic web url:

http://www.example.com/folder/subfolder/somemorefolder/document.txt

What i need is, that i can execute a SQL Statement, which removes the last "/" and everything after this char, so that the result looks like this:

http://www.example.com/folder/subfolder/somemorefolder

I found a solution for MySQL but this does not work for SQL Server:

SELECT TRIM(TRAILING CONCAT('/',SUBSTRING_INDEX(path, '/', -1)) FROM path)
FROM   my_table;

I have also tried this SQL Statement, but this also didnt work:

SELECT SUBSTRING([full_url ], len([full_url ]) - charindex('/', reverse([full_url ]))) from myTable

CodePudding user response：

Your CHARINDEX() trick, with the help of REVERSE(), can work with some modification:

SELECT
    full_url,
    REVERSE(SUBSTRING(REVERSE(full_url),
                      CHARINDEX('/', REVERSE(full_url))   1,
                      LEN(full_url))
    ) AS partial_url
FROM yourTable;

CodePudding user response：

Another option using the same functions as used by Tim:

SELECT 
    full_url,
    SUBSTRING(full_url,
              1,
              (LEN(full_url) - CHARINDEX('/',REVERSE(full_url)))
    ) AS partial_url
FROM yourTable;