picking off data from a string

Question

Need help figuring out how exactly to break down a filepath to get the relevant details,

For ex: I have a fielapath like '\192.168.50.100\folder\march\filetypeA\filenameB

How do I extract the month, filename and filetype separately from this type of a string?
expected out put is:

March FiletypeA FilenameB

That should be in your question, not the comments. Is that 1 column, or 3? — Thom A, Aug 16 '19 at 19:30
Possible duplicate of [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) — OwlsSleeping, Aug 16 '19 at 19:32
Can you provide more than one example? Will the format always be `'\{IP Address}\{Share Name}\{Month}\{File Type}\{File Name}'`? Normally shares begin with `\\ ` not `\ `; are you sure the string you have supplied is correct? — Thom A, Aug 16 '19 at 19:32
Hello, @Larnu, yes, you are right, these are 3 separate columns and the format will mostly be as mentioned. — priyesh a, Aug 16 '19 at 19:38

score 2 · Answer 1 · answered Aug 16 '19 at 19:49

Are you looking for something like

WITH Mnths AS
(
  SELECT DATENAME(Month, DATEADD(Month, M - 1, 0)) MName
  FROM
  (
    VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
  ) T(M)
)
SELECT REPLACE(RIGHT(T.V, LEN(V) - CHARINDEX(M.MName, T.V) + 1), '\', ' ') Result
FROM Mnths M JOIN
(
  VALUES
  ('\192.168.50.100\folder\march\filetypeA\filenameB'),
  ('\192.168.50.100\folder\August\filetypeA\filenameB')
) T (V) 
ON T.V LIKE CONCAT('%\', M.MName, '%');

Which will returns:

+----------------------------+
|           Result           |
+----------------------------+
| march filetypeA filenameB  |
| August filetypeA filenameB |
+----------------------------+

Demo

picking off data from a string

1 Answers1