CODEWITHSUNDEEP
sqlsql-server
Mr.Human
Mr.Human

Reputation: 617

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: '\\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:

  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 ('\\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\')

Upvotes: 0

Views: 501

Answers (3)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

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      |
+----+---------------------------------------+--------------------------------------+

Upvotes: 1

eshirvana
eshirvana

Reputation: 24593

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'

Upvotes: 1

Bjorg P
Bjorg P

Reputation: 1073

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

Upvotes: 2

Related Questions