I have 2 unassociated tables.

Users , Files

In Users I have stuff like Name, EMail, ID. In Files I have things like File Name, Duraiotn, URL.

The file's URL is in this type of format "/Files/UsersID/Videos/Filename.mp4"

I want to inner join the 2 tables on the Users.ID = Files.URL but the sub string of the URL. So I need to remove "/Files/" and remove "/V*" and every thing after it to isolate the UsersID.

So then I can make a table to show Users.Name, Files.Name, Files.Duration"

I have been trying to figure this out for the past 3 days and am about to give up. Is this type of action possible? I dont have the option to edit the database sadly.

CodePudding user response：

Using the following tables:

CREATE TABLE `users` (
    `id` INT NOT NULL
  );
  
INSERT INTO `users` (`id`) VALUES (1), (100), (2), (200);


CREATE TABLE `files` (
    `path` varchar(255) NOT NULL
  );
  
INSERT INTO `files` (`path`) VALUES 
  ("/Files/1/Videos/Filename1.mp4"), 
  ("/Files/100/Videos/Filename100.mp4"), 
  ("/Files/3/Videos/Filename3.mp4");

You can use this selects:

SELECT SUBSTRING(`path`, 8) As `path starting from user id` FROM `files`;

SELECT SUBSTRING(`path`, 8,  LOCATE("/", SUBSTRING(`path`, 8)) - 1) As `user id from path` FROM `files`;

SELECT `users`.`id`, `files`.`path` 
FROM `users` 
LEFT JOIN `files` ON (`users`.`id` = SUBSTRING(`path`, 8,  LOCATE("/", SUBSTRING(`path`, 8)) - 1));

The result of the last select to answer your question:

Link to SQLFiddle: http://sqlfiddle.com/#!9/09872b/6

CodePudding user response：

Probably better ways to do this which I'll leave to those will better sql skills than mine but here's an idea, get position of the 2 and 3 / and then split out the user ID

--drop table #T
with cte_tableTest
as
(
select '/MyFiles/Dave123/Videos/Filename.mp4' as img
union
select '/MyFiles2/Bob1/VideosFormats/MyFilename.mp4' as img
union
select '/MyFiles78/Harry5655/VideosFormats56/MyFilename.mp4' as img
union
select '/MyFiles92/G12/VideosFormatsTest/hsdhdshdFilename.mp4' as img
)

select * 
into #T
from cte_tableTest
;

-- Code Start

;with T as (
    select 0 as row, charindex('/', img) pos, img,1 as loopnum from #T
    union all
    select pos   1, charindex('/', img, pos   1), img, loopnum   1
    from T
    where pos > 0
)
,cte_stringPositions
as
(
    select img 
           ,max(len(img)) as [StringLen]
           ,max(case when loopnum = 2 then pos 1 end) as Startpos
           ,max(case when loopnum = 3 then len(img)-pos end) as Endpos
    from T 
    where pos > 0  
    and loopnum in (2,3)
    group by img
)

select substring(img,startpos,([StringLen]-(Startpos Endpos))) as UserID
from cte_stringPositions

CodePudding user response：

I ended up using this in the FROM section.

FROM 
  LEFT JOIN Users ON LEFT(REPLACE(Files.Url, '/Files/', ''), CHARINDEX('/', REPLACE(Files.Url, '/Files/', '')) - 1) LIKE Users.ID