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

Question

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

ID - INT
DocLocation - NTEXT

DocLocation has values in following format: '\\fileShare1234\storage\ab\xyz.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 '\\fileShare1234\storage\ab\xyz.ext' should be updated to '\\newFileShare\storage\ab\xyz.ext'

Two questions:

How do I query my DocLocation to extract DocLocations with unique file share values? Like 'fileShare1234' and 'fileShare6789' and so on..
In a single Update query how do I update my DocLocation values to newFileShare ('\\fileShare1234\storage\ab\xyz.ext' to '\\newFileShare\storage\ab\xyz.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\')

You really need to change that NTEXT column to NVARCHAR(MAX). This can be done using stuff: stuff(cast(DocLocation As nvarchar(max)), 3, charindex('\', DocLocation, 3) - 1, 'newFileShare'). — Jeff
– Jeff, Commented Aug 19, 2021 at 22:13

Bjorg P · Accepted Answer · 2021-08-19 16:12:30Z

The first step is fairly easy. If all your paths begin with \\, then you can find all the DISTINCT servers using SUBSTRING. I will make a simple script with a table variable to replicate some data. The value of 3 is in the query and it is the length of \\ plus 1 since SQL Server counts from 1.

DECLARE @Documents AS TABLE(
    ID INT NOT NULL, 
    DocLocation NTEXT NOT NULL
);

INSERT INTO @Documents(ID, DocLocation) 
VALUES (1,'\\fileShare56789\storage\ab\xyz.ext'),
(2,'\\fileShare1234\storage\ab\cd\xyz.ext'), 
(3,'\\share4567890\w\x\y\z\file.ext');

SELECT DISTINCT SUBSTRING(DocLocation, 3, CHARINDEX('\', DocLocation, 3) - 3) AS [Server] 
FROM @Documents;

The results from this are:

Server
fileShare1234
fileShare56789
share4567890

For the second part, we can just concatenate the new server name with the path that appears after the first \.

UPDATE @Documents 
SET DocLocation = CONCAT('\\newfileshare\',
    SUBSTRING(DocLocation, 3, LEN(CAST(DocLocation AS nvarchar(max))) - 2));

SELECT * FROM @Documents;

For some reason I cannot create a table with the results here, but the values I see are this:

\\newfileshare\fileShare56789\storage\ab\xyz.ext
\\newfileshare\fileShare1234\storage\ab\cd\xyz.ext
\\newfileshare\share4567890\w\x\y\z\file.ext

Yitzhak Khabinsky · Accepted Answer · 2021-08-19 17:02:04Z

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 
('\\fileShare56789\storage\ab\xyz.ext'),
('\\fileShare1234\storage\ab\cd\xyz.ext'), 
('\\share4567890\w\x\y\z\file.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 | \\fileShare56789\storage\ab\xyz.ext   | \\newFileShare\storage\ab\xyz.ext    |
|  2 | \\fileShare1234\storage\ab\cd\xyz.ext | \\newFileShare\storage\ab\cd\xyz.ext |
|  3 | \\share4567890\w\x\y\z\file.ext       | \\newFileShare\w\x\y\z\file.ext      |
+----+---------------------------------------+--------------------------------------+

eshirvana · Accepted Answer · 2021-08-19 16:12:34Z

1

to get the unique list of shared folder path , you can use this query:

SELECT distinct SUBSTRING(DocLocation,0,CHARINDEX('\',DocLocation,3))
from Documents

and your update command should work and yes you can merge copuple of replace update but better to run them seperately

update Documents  
set DocLocation = REPLACE(DocLocation,'\\fileShare1234','\\newFileShare')

but I recommend you always record relative address instead of full path like: \storage\ab\xyz.ext'

answered Aug 19, 2021 at 16:12

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related