Find and replace by pattern-CodePudding

I have a table that has a column like the one below

url
----------------
dir=mp3\cat152AB&amp;fileName=file-01.mp3
dir=mp3\cat2500DfDD00&amp;fileName=file-02.mp3
dir=mp3\cat4500f0655&amp;fileName=file-03.mp3
...

How can I delete extra strings and arrange the fields as follows in SQL Server.

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

CodePudding user response：

you can use charindex and substring :

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

CodePudding user response：

CHARINDEX and SUBSTRING can help you, please check the example:

select substring (field, charindex (';fileName=', field)   len (';fileName='), len (field) - len ('.mp3')   1 - charindex (';fileName=', field) - len (';fileName='))
from (
select 'dir=mp3\cat152AB&amp;fileName=file-01.mp3' field union all
select 'dir=mp3\cat2500DfDD00&amp;fileName=file-02.mp3' union all
select 'dir=mp3\cat4500f0655&amp;fileName=file-03.mp3'
) a