Extract name from UNC Path

Question

Post reply

Extract name from UNC Path

bdmelvil

Newbie

Points: 7
More actions
February 4, 2015 at 2:03 pm

#300853

Hello Everyone
Running into some difficulty trying to extract a name from a UNC Path. The structure of the UNC Path is this:
\\server1-1\share\username\this\part\goes\on\and\on
I'm needing to extract "username" into a separate column.
Any help would be appreciated.
Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

wow, i fiddled with doing this with substring and charindex, and it is so ugly compared to using a delimitedsplit.

/*--Results

(No column name) UNC ITem

username1 \\server1-1\share\username1\this\part\goes\on\and\on username1

bill \\server1-1\share\bill\this bill

bartholemew \\server1-1\share\bartholemew\this bartholemew

username2 \\server1-1\share\username2\this username2

username3 \\server1-1\share\username3\this\part username3

username4 \\server1-1\share\username4\this\part\goes username4

*/

With MyCTE(UNC)

AS

(

SELECT '\\server1-1\share\username1\this\part\goes\on\and\on' UNION ALL

SELECT '\\server1-1\share\bill\this' UNION ALL

SELECT '\\server1-1\share\bartholemew\this' UNION ALL

SELECT '\\server1-1\share\username2\this' UNION ALL

SELECT '\\server1-1\share\username3\this\part' UNION ALL

SELECT '\\server1-1\share\username4\this\part\goes'

)

--we ASSUME the '\share\' starts the name, and the trailing ' ends it

SELECT SUBSTRING(UNC,LEN('\\server1-1\share\') + 1,CHARINDEX('\',unc,LEN('\\server1-1\share\') + 1 ) - LEN('\\server1-1\share\') -1),*

FROM MyCTE

CROSS APPLY(SELECT ITem from dbo.DelimitedSplit8K(unc,'\') WHERE ItemNumber = 5 ) x1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bdmelvil Newbie Points: 7 More actions · Answer 2

Tested and successful on attempt 1, thanks a million!!!