CODEWITHSUNDEEP
sqlsql-server
Andy
Andy

Reputation: 50560

Select portion of string

I have a set of data that contains full UNC paths to directories. I want to select just the path (removing the server/IPs). How can I do this?

Data:

\\nas.home.example.com\dvd\My First Video
\\nas.office.example.com\business\Report1
\\nas.office.example.com\business\Report2
\\10.10.10.10\projects\proj1\images
\\10.10.10.10\projects\proj1\queries
\\10.10.10.10\projects\proj1\output

Expected Results (can be with or without the leading \, I don't care):

\dvd\My First Video
\business\Report1
\business\Report2
\projects\proj1\images
\projects\proj1\queries
\projects\proj1\output

I've tried the following (specific to one of the above inputs):

SELECT RIGHT(('\\nas.home.example.com\dvd\My First Video'), 
  CHARINDEX('\', REVERSE('\\nas.home.example.com\dvd\My First Video')));

This returns just the inner most directory (My First Video). I can add an offset to get the parent, but it only works if the directory is exactly that number of characters:

SELECT RIGHT(('\\nas.home.example.com\dvd\My First Video'), 
  CHARINDEX('\', REVERSE('\\nas.home.example.com\dvd\My First Video'))+4);

Returns \dvd\My First Video, which is good - for that one value. How can I modify my query to work for all of my data values?

I suspect I may actually need two of these queries. One for the servers with a DNS name, that all end in .com and one for the IP address, which all start with 10.10

Upvotes: 0

Views: 182

Answers (3)

Sparky
Sparky

Reputation: 15075

Same concept as Aaron, slightly different syntax

SUBSTRING(p, CHARINDEX('\',p,3), 255)

Upvotes: 1

Tom Hamming
Tom Hamming

Reputation: 10961

Try this (the important part is the third line):

DECLARE @str varchar(100)
SET @str = '\\nas.home.example.com\dvd\My First Video'
select substring(@str, CHARINDEX('\', @str, 3), len(@str) - charindex('\', @str, 3))

Replace @str with the column name in your SELECT statement.

What this is doing is grabbing a substring of the string starting at the first index of '\' after the third character and having the length of the total length of the string minus the first index of '\' after the third.

Upvotes: 0

anon
anon

Reputation: 

DECLARE @x TABLE (p VARCHAR(255))

INSERT @x SELECT '\\nas.home.example.com\dvd\My First Video'
UNION ALL SELECT '\\nas.office.example.com\business\Report1'
UNION ALL SELECT '\\nas.office.example.com\business\Report2'
UNION ALL SELECT '\\10.10.10.10\projects\proj1\images'
UNION ALL SELECT '\\10.10.10.10\projects\proj1\queries'
UNION ALL SELECT '\\10.10.10.10\projects\proj1\output'
UNION ALL SELECT 'foo.bar\whatever\who';

SELECT p, x = SUBSTRING(p, CHARINDEX('\', SUBSTRING(p, 3, 4000)) + 2, 4000) FROM @x;

Results:

p                                           x
-----------------------------------------   ------------------------
\\nas.home.example.com\dvd\My First Video   \dvd\My First Video
\\nas.office.example.com\business\Report1   \business\Report1
\\nas.office.example.com\business\Report2   \business\Report2
\\10.10.10.10\projects\proj1\images         \projects\proj1\images
\\10.10.10.10\projects\proj1\queries        \projects\proj1\queries
\\10.10.10.10\projects\proj1\output         \projects\proj1\output
foo.bar\whatever\who                        \whatever\who

If you want to remove the leading \, change + 2 to + 3.

Upvotes: 1

Related Questions