我在表中有一個 URL 列，下面是 URL。我想在 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
預期產出
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
嘗試過charindex但無法成功。任何建議將不勝感激。
uj5u.com熱心網友回復：
你在 Location 之后檢測到“/”的索引，就像這樣
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
uj5u.com熱心網友回復：
請嘗試以下解決方案。
它使用以下演算法：
- 將 URL 標記為 XML。
- 獲取包含“Location”的 XML 元素的位置。
- 檢索 XML 元素到上述步驟的位置，然后重新組合 URL。
查詢陳述句
-- 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) ;
輸出
---- ----------------------------------------------------------------------------
| 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 |
---- ----------------------------------------------------------------------------
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/389650.html
標籤：sql sql-server 查询语句