T-SQL Update Random String Value in Column

Question

I have a table that contains a file path column containing data like this.

C:\ABC\Files\AA.dat
C:\ABC\Files\AA.Unbound.qp
C:\DEF\AA\GGG Build 1 Modules\Random123.qpp
C:\DEF\AA\GGG Build 1 Modules\Random456.qpp
C:\GH\DC.Random789.qpp
C:\IJK\Random987.qpp

I need to replace the file path to a generic path before the actual file name, so everything before the final \ needs to change but keep the same file names, something like this

D:\FILES\AA.dat
D:\FILES\AA.Unbound.qpp
D:\FILES\Random123.qpp
D:\FILES\Random456.qpp
D:\FILES\DC.Random789.qpp
D:\FILES\Random987.qpp

Table has around 2000 rows and if possible I want to update in a single script. Running SQL 2008R2 and this is first stage of finally upgrading so any help would be appreciated.

Answer 1

One possible approach is to reverse the full filenames and find the actual file names.

Table:

CREATE TABLE Data ([FileName] varchar(100))
INSERT INTO Data([FileName])
VALUES
 ('C:\ABC\Files\AA.dat'),
 ('C:\ABC\Files\AA.Unbound.qp'),
 ('C:\DEF\AA\GGG Build 1 Modules\Random123.qpp'),
 ('C:\DEF\AA\GGG Build 1 Modules\Random456.qpp'),
 ('C:\GH\DC.Random789.qpp'),
 ('C:\IJK\Random987.qpp'),
 ('Random000.qpp')

Statement:

UPDATE Data
SET [FileName] = CONCAT(
 'D:\FILES\',
 REVERSE(LEFT(REVERSE('\' + [FileName]), CHARINDEX('\', REVERSE('\' + [FileName])) - 1))
)

Result:

FileName
D:\FILES\AA.dat
D:\FILES\AA.Unbound.qp
D:\FILES\Random123.qpp
D:\FILES\Random456.qpp
D:\FILES\DC.Random789.qpp
D:\FILES\Random987.qpp
D:\FILES\Random000.qpp

• Stockburn: Thanks Zhorov will give this was a crack now
• Stockburn: Zhorov, tested in a sandpit environment and works like a charm, thank you.

Answer 2

I have written something that has worked, gives me just the file name after the final \ character. So I should be able to build an update statement.

SELECT Column1 ,Column2 ,FilePath ,RIGHT(FilePath,CHARINDEX('\',REVERSE(FilePath))+0) file_name FROM TableName

Open to advice if this is best way to go