unc_file_path VARCHAR(256)

)

INSERT INTO test SELECT '\\servername\folder1\FTP\folder2/111\862450_FileBundle.zip';

INSERT INTO test SELECT '\\servername\folder1\FTP\folder2\222/862450_FileBundle.zip';

INSERT INTO test SELECT '\\servername\folder1\FTP\folder2\333\862450_FileBundle.zip';

INSERT INTO test SELECT '\\servername\folder1\FTP\folder2/444/862450_FileBundle.zip';

INSERT INTO test SELECT '\\servername\folder1\FTP\folder2\xxx/862450_FileBundle.zip';

*,

SUBSTRING(test.unc_file_path, headMatch.pos+1, headMatch.chars)

FROM

test

OUTER APPLY

(

SELECT

MIN(pos), MIN(chars)

FROM

(

SELECT

PATINDEX('%' + head + body + tail + '%', test.unc_file_path) AS pos, chars

FROM

(

SELECT '\'

UNION ALL SELECT '/'

)

head(head)

CROSS JOIN

(

SELECT 1, '[0-9]'

UNION ALL SELECT 2, '[0-9][0-9]'

UNION ALL SELECT 3, '[0-9][0-9][0-9]'

UNION ALL SELECT 4, '[0-9][0-9][0-9][0-9]'

UNION ALL SELECT 5, '[0-9][0-9][0-9][0-9][0-9]'

)

body(chars, body)

CROSS JOIN

(

SELECT '\'

UNION ALL SELECT '/'

)

tail(tail)

)

match