SQL Concatenation

Question

Hi

I am writing a proc to handle backup location for a restore script. can anyone suggest me how to generate complete path using both locations as below and final path name silty different as we need to ignore first part from location 2 ( i.e K:\backup)

Backup location1 : \backupserver\sqlbackup

Backup location 2 : K:\Backups\localserver\PRD_DW_ABCD\FULL\localserver_PRD_DW_ABCD_FULL_20210621_140002_1.bak

I am looking for final location by using both backup locations (concatenation and remove part of k:\backup from send backup location )

Path should be as

\backupserver\sqlbackup\localserver\PRD_DW_ABCD\FULL\localserver_PRD_DW_ABCD_FULL_20210621_140002_1.bak

Answer 1

More options...

DECLARE @location1 VARCHAR(1024)
DECLARE @location2 VARCHAR(1024)
SET @location1 = '\\backupserver\sqlbackup'
SET @location2 = 'K:\Backups\localserver\PRD_DW_ABCD\FULL\localserver_PRD_DW_ABCD_FULL_20210621_140002_1.bak'

-- Option1: fit if @location2 always start with "K:\Backups\"
SELECT @location1 + REPLACE(@location2, 'K:\Backups\', '')

-- Option2: if you do not know the exact path of location2 Disk unknow) and you only have know you need to remove pattern <disk>:\<a word come here>\
SELECT @location1 + substring(@location2, CHARINDEX('\', @location2, 4)-1, len(@location2) )
GO

Answer 2

Hi @@Narsimha rao ,

Welcome to the microsoft TSQL forum!

Please also check the following methods:

DECLARE @location1 VARCHAR(MAX)  
DECLARE @location2 VARCHAR(MAX)  
SET @location1 = '\\backupserver\sqlbackup'  
SET @location2 = 'K:\Backups\localserver\PRD_DW_ABCD\FULL\localserver_PRD_DW_ABCD_FULL_20210621_140002_1.bak'  
      
SELECT @location1+RIGHT(@location2,LEN(@location2)-PATINDEX('%[a-z]\%',@location2))  

Output:

\\backupserver\sqlbackup\localserver\PRD_DW_ABCD\FULL\localserver_PRD_DW_ABCD_FULL_20210621_140002_1.bak  

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

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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

SELECT @location1 + substring(@location2, len('K:\Backups')+1, len(@location2)