SQL – Replace a particular part of column string value (between second and third slash)

In my SQLServer DB, I have a table called Documents with the following columns:

1. ID – INT
2. DocLocation – NTEXT

DocLocation has values in following format: ‘\fileShare1234storageabxyz.ext’

Now it seems these documents are stored in multiple file share paths. We’re planning to migrate all documents in one single file share path called say ‘newFileShare’ while maintaining the internal folder structure.

So basically ‘\fileShare1234storageabxyz.ext’ should be updated to ‘\newFileSharestorageabxyz.ext’

Two questions:

1. How do I query my DocLocation to extract DocLocations with unique file share values? Like ‘fileShare1234’ and ‘fileShare6789’ and so on..
2. In a single Update query how do I update my DocLocation values to newFileShare (‘\fileShare1234storageabxyz.ext’ to ‘\newFileSharestorageabxyz.ext’)

I think the trick would be extract and replace text between second and third slashes.

I’ve still not figured out how to achieve my first objective. I require those unique file shares for some other tasks.

As for the second objective, I’ve tried using replace between it will require multiple update statements. Like I’ve done as below:

update Documents  set DocLocation = REPLACE(Cast(DocLocation as NVarchar(Max)), '\fileShare1234', '\newFileShare')

Answer

Please try the following solution based on XML and XQuery.

Their data model is based on ordered sequences. Exactly what we need while processing fully qualified file path: [position() ge 4]

When you are comfortable, just run the UPDATE statement by updating the DocLocation column with the calculated result.

It is better to use NVARCHAR(MAX) instead of NText data type.

SQL

-- DDL and sample data population, start
DECLARE @tbl AS TABLE(ID INT IDENTITY PRIMARY KEY, DocLocation NVARCHAR(MAX));
INSERT INTO @tbl(DocLocation) VALUES 
('\fileShare56789storageabxyz.ext'),
('\fileShare1234storageabcdxyz.ext'), 
('\share4567890wxyzfile.ext');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ''
    , @newFileShare NVARCHAR(100) = 'newFileShare';

SELECT ID, DocLocation
    , result = '\' + @newFileShare + @separator +
        REPLACE(c.query('data(/root/r[position() ge 4]/text())').value('text()[1]', 'NVARCHAR(MAX)'), SPACE(1), @separator) 
FROM @tbl
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(DocLocation, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t(c);

Output

+----+---------------------------------------+--------------------------------------+
| ID |              DocLocation              |                result                |
+----+---------------------------------------+--------------------------------------+
|  1 | \fileShare56789storageabxyz.ext   | \newFileSharestorageabxyz.ext    |
|  2 | \fileShare1234storageabcdxyz.ext | \newFileSharestorageabcdxyz.ext |
|  3 | \share4567890wxyzfile.ext       | \newFileSharewxyzfile.ext      |
+----+---------------------------------------+--------------------------------------+