question

Narsimharao-9685 avatar image
0 Votes"
Narsimharao-9685 asked EchoLiu-msft edited

SQL Concatenation

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.

Regards
Echo

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

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







5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
SELECT @location1 + substring(@location2, len('K:\Backups')+1, len(@location2)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @@Narsimharao-9685,

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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.