Removing a part of URL from coulmn in SQL-CodePudding

I have a URL column in the table and below are the URL's. I want to remove the string after Location.

https://xyz.sharepoint.com/sites/tender/lp/46/Lists/PlaceDetails/Location3
https://xyz.sharepoint.com/sites/tender/lp/50/Lists/PlaceDetails/Location2/4_.000
https://xyz.sharepoint.com/sites/tender/lp/52/Lists/PlaceDetails/Location5
https://xyz.sharepoint.com/sites/tender/lp/50/Lists/PlaceDetails/Location6/8_.000

Expected OutPut

https://xyz.sharepoint.com/sites/tender/lp/46/Lists/PlaceDetails/Location3
https://xyz.sharepoint.com/sites/tender/lp/50/Lists/PlaceDetails/Location2
https://xyz.sharepoint.com/sites/tender/lp/52/Lists/PlaceDetails/Location5
https://xyz.sharepoint.com/sites/tender/lp/50/Lists/PlaceDetails/Location6

Tried with charindex but couldn't get succeeded. Any suggestion would be greatly appreciated.

CodePudding user response：

You detect the index of "/" after Location, something like this

DECLARE @DATA NVARCHAR(200) = 'https://xyz.sharepoint.com/sites/tender/lp/50/Lists/PlaceDetails/Location2/4_.000'

SELECT CASE 
    WHEN charindex('/', @data, charindex('Location', @data)) = 0
        THEN @data
    ELSE LEFT(@data, charindex('/', @data, charindex('Location', @data)) - 1)
    END

CodePudding user response：

Please try the following solution.

It is using the following algorithm:

Tokenize URL as XML.
Get position of the XML element the contains "Location".
Retrieve XML elements up to the position from the step above, and reassemble URL back.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [URL] VARCHAR(MAX));
INSERT INTO @tbl ([URL]) VALUES
('https://xyz.sharepoint.com/sites/tender/lp/46/Lists/PlaceDetails/Location3'),
('https://xyz.sharepoint.com/sites/tender/lp/50/Lists/PlaceDetails/Location2/4_.000'),
('https://xyz.sharepoint.com/sites/tender/lp/52/Lists/PlaceDetails/Location5'),
('https://xyz.sharepoint.com/sites/tender/lp/50/Lists/PlaceDetails/Location6/8_.000');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '/'

SELECT t.* 
    , REPLACE(c.query('data(/root/r[position() le sql:column("t2.pos")]/text())')
        .value('.', 'VARCHAR(MAX)')
        ,SPACE(1),@separator) AS Result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
        REPLACE([URL], @separator, ']]></r><r><![CDATA[')   
        ']]></r></root>' AS XML)) AS t1(c)
CROSS APPLY (SELECT c.query('for $i in /root/r[contains(.,"Location")]
      let $pos := count(root/*[. << $i])   1
      return $pos').value('.','INT')) AS t2(pos)        ;

Output

 ---- ---------------------------------------------------------------------------- 
| ID |                                   Result                                   |
 ---- ---------------------------------------------------------------------------- 
|  1 | https://xyz.sharepoint.com/sites/tender/lp/46/Lists/PlaceDetails/Location3 |
|  2 | https://xyz.sharepoint.com/sites/tender/lp/50/Lists/PlaceDetails/Location2 |
|  3 | https://xyz.sharepoint.com/sites/tender/lp/52/Lists/PlaceDetails/Location5 |
|  4 | https://xyz.sharepoint.com/sites/tender/lp/50/Lists/PlaceDetails/Location6 |
 ---- ----------------------------------------------------------------------------