Extracting value in between two of the same characters in SQL

Question

I have a nvarchar column that is a file path. For example, the values look like the following:

/Example Folder/Example Subfolder/ Example File

I want to pull out the first directory name (so for this example, I just want:

Example Folder

Any suggestions on how to best extract this in SSMS?

Answer 1

Declare @ColumnName nvarchar(300);
Set @ColumnName = '/Example Folder/Example Subfolder/ Example File'

Select SUBSTRING(@ColumnName,2,CHARINDEX('/',@ColumnName,2)-2) as [First Directory Name]

Output:

First Directory Name
----------------------
Example Folder

To suit it to your needs, replace @ColumnName with the ColumnName from the table and add from tableName to the select query.

Answer 2

SELECT SUBSTRING(columnname, 0 , CHARINDEX('/',columnname,2))
FROM tableName

db<>fiddle here

Answer 3

Please try the following generic method.

It is using XML and XQuery to tokenize a string. XML/XQuery data model is based on ordered sequences. Exactly what we need for the task.

Both separator and token's position are parameterized. So, it is very easy to retrieve any token in any position.

SQL

-- DDL and sample data population, start
DECLARE @tbl AS TABLE(ID INT IDENTITY PRIMARY KEY, tokens NVARCHAR(MAX));
INSERT INTO @tbl(tokens) VALUES 
('/Example Folder/Example Subfolder/Example File'),
('/SampleFolder/SampleSubfolder/SampleFile');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '/'
   , @position INT = 1;

SELECT *
    , c.value('(/root/r[text()][sql:variable("@position")]/text())[1]', 'VARCHAR(256)') AS token
FROM @tbl
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t(c);

Output

+----+------------------------------------------------+----------------+
| ID |                     tokens                     |     token      |
+----+------------------------------------------------+----------------+
|  1 | /Example Folder/Example Subfolder/Example File | Example Folder |
|  2 | /SampleFolder/SampleSubfolder/SampleFile       | SampleFolder   |
+----+------------------------------------------------+----------------+

Answer 4

Simple:

SELECT REPLACE(LEFT(folder, CHARINDEX('/', folder, 2)), '/', '')