Replace string with server name instance

Question

Hi connections,

Please help me with this query
Have configuration data in table having filepath, archival path,rejectpath
Eg: D:\Shrp_utilities\PFIX\data\inbound\

In above example need to update PFIX to UAT dynamically using server name

Answer 1

Hi @vikram vikky ,

Based on my guess, please try the following solution.
It will replace any file path part based on its position in the fully qualified file path.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, filepath VARCHAR(255));  
INSERT INTO @tbl (filepath) VALUES  
('D:\Shrp_utilities\PFIX\data\inbound\'),  
('D:\Shrp_utilities\PFIX\data\outbound\');  
-- DDL and sample data population, end  
  
DECLARE @separator CHAR(1) = '\'  
	, @position INT = 3  
	, @localServer VARCHAR(100) = (SELECT @@SERVERNAME);  
  
SELECT @localServer;  
  
SELECT t.*  
	, REPLACE(c.query('  
	for $x in /root/r  
	return if ($x is (/root/r[sql:variable("@position")])[1]) then sql:variable("@localServer")  
	else data($x)  
	').value('.', 'VARCHAR(255)')  
	, SPACE(1), @separator) AS Result  
FROM @tbl AS t  
CROSS APPLY (SELECT TRY_CAST('<root><r>' +   
        REPLACE(filepath, @separator, '</r><r>') +   
        '</r></root>' AS XML)) AS t1(c);  

Output

+----+---------------------------------------+--------------------------------------------+  
| ID |               filepath                |                   Result                   |  
+----+---------------------------------------+--------------------------------------------+  
|  1 | D:\Shrp_utilities\PFIX\data\inbound\  | D:\Shrp_utilities\SPACESHIP\data\inbound\  |  
|  2 | D:\Shrp_utilities\PFIX\data\outbound\ | D:\Shrp_utilities\SPACESHIP\data\outbound\ |  
+----+---------------------------------------+--------------------------------------------+  

Answer 2

Please check:

SELECT LEFT('D:\Shrp_utilities\PFIX\data\inbound\',  
PATINDEX('%[a-z]\%\%','D:\Shrp_utilities\PFIX\data\inbound\')+1)  
+ (SELECT @@SERVERNAME)+   
RIGHT('D:\Shrp_utilities\PFIX\data\inbound\',  
LEN('D:\Shrp_utilities\PFIX\data\inbound\')-  
CHARINDEX('\','D:\Shrp_utilities\PFIX\data\inbound\',  
PATINDEX('%[a-z]\%\%','D:\Shrp_utilities\PFIX\data\inbound\')+2)+1)  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

A somewhat simpler way:

SELECT replace(filepath, '\PFIX\', '\' + @servernname + '\') AS replacedname