查找并替换为模式

Question

我有一个表，它有一个像下面这样的列

url
----------------
dir=mp3cat152AB&fileName=file-01.mp3
dir=mp3cat2500DfDD00&fileName=file-02.mp3
dir=mp3cat4500f0655&fileName=file-03.mp3
...

如何在SQL Server中删除多余的字符串并按如下方式排列字段。

url
----------------
file-01
file-02
file-03
...

Answer 1

您可以使用charindex和substring:

SELECT substring ('dir=mp3cat152AB&fileName=file-01.mp3', CHARINDEX('fileName=', 'dir=mp3cat152AB&fileName=file-01.mp3') +9 ,  
LEN('dir=mp3cat152AB&fileName=file-01.mp3')-CHARINDEX('fileName=', 'dir=mp3cat152AB&fileName=file-01.mp3') 
) AS MatchPosition;

Answer 2

CHARINDEX和SUBSTRING可以帮助您，请查看示例:

select substring (field, charindex (';fileName=', field) + len (';fileName='), len (field) - len ('.mp3') + 1 - charindex (';fileName=', field) - len (';fileName='))
from (
select 'dir=mp3cat152AB&fileName=file-01.mp3' field union all
select 'dir=mp3cat2500DfDD00&fileName=file-02.mp3' union all
select 'dir=mp3cat4500f0655&fileName=file-03.mp3'
) a

Answer 3

您想要的信息似乎总是在字符串结束前的第11到第5个字符。我建议一个简单的解决方案:

select left(right(url, 11), 7)

这是一个db<>小提琴

Answer 4

请尝试以下方法。

通过XML/XQuery使用标记化。

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, url VARCHAR(255));
INSERT INTO @tbl (url) VALUES
('dir=mp3cat152AB&fileName=file-01.mp3'),
('dir=mp3cat2500DfDD00&fileName=file-02.mp3'),
('dir=mp3cat4500f0655&fileName=file-03.mp3');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = '=';
SELECT id, url
, LEFT(x, CHARINDEX('.', x) - 1) AS Result
FROM @tbl
CROSS APPLY (SELECT CAST('<root><r><![CDATA[' + 
REPLACE(url, @separator, ']]></r><r><![CDATA[') + 
']]></r></root>' AS XML)) AS t1(c)
CROSS APPLY (VALUES (c.value('(/root/r[last()]/text())[1]', 'VARCHAR(100)'))) AS t2(x);

+----+------------------------------------------------+---------+
| id |                      url                       | Result  |
+----+------------------------------------------------+---------+
|  1 | dir=mp3cat152AB&fileName=file-01.mp3      | file-01 |
|  2 | dir=mp3cat2500DfDD00&fileName=file-02.mp3 | file-02 |
|  3 | dir=mp3cat4500f0655&fileName=file-03.mp3  | file-03 |
+----+------------------------------------------------+---------+

Answer 5

我知道我们有一个公认的答案，但我想用另一个简单，高性能的解决方案来解决各种长度的文件名和文件扩展名。为了好玩，我包含了一个参数，它允许您在选择时包含文件扩展名。

--==== Easily Consumable Sample Data
DECLARE @link TABLE ([url] VARCHAR(100) UNIQUE);
INSERT  @link VALUES ('dir=mp3cat152AB&fileName=file-01.mp3'),
('dir=mp3cat2500DfDD00&fileName=file-02.mp3'),
('dir=mp3cat4500f0655&fileName=file-03.mp3'),
('dir=mp3cat4500f0655&fileName=file-999.mp3'),
('dir=mp3cat4500d9997&fileName=file-0021.prodigi');
--==== Allows you to determine if you want the file extension
DECLARE @exclude BIT=1;
SELECT      l.[url], TheFile = SUBSTRING(l.[url], s.Pos, s.Ln-s.Pos- ((@exclude*(fl.Ln)-1)))
FROM        @link AS l
CROSS APPLY (VALUES(CHARINDEX('.',REVERSE(l.[url])))) AS fl(Ln)
CROSS APPLY (VALUES(CHARINDEX('fileName=',l.[url])+9, LEN(l.[url]))) AS s(Pos,Ln);

@exclude = 1的回报:

url                                                   TheFile
----------------------------------------------------- --------------
dir=mp3cat152AB&fileName=file-01.mp3             file-01
dir=mp3cat2500DfDD00&fileName=file-02.mp3        file-02
dir=mp3cat4500d9997&fileName=file-0021.prodigi   file-0021
dir=mp3cat4500f0655&fileName=file-03.mp3         file-03
dir=mp3cat4500f0655&fileName=file-999.mp3        file-999

@exclude = 0的回报:

url                                                   TheFile
----------------------------------------------------- --------------
dir=mp3cat152AB&fileName=file-01.mp3             file-01.mp3
dir=mp3cat2500DfDD00&fileName=file-02.mp3        file-02.mp3
dir=mp3cat4500d9997&fileName=file-0021.prodigi   file-0021.prodigi
dir=mp3cat4500f0655&fileName=file-03.mp3         file-03.mp3
dir=mp3cat4500f0655&fileName=file-999.mp3        file-999.mp3