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.

Zhorov · Accepted Answer · 2020-04-02 05:49:35Z

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

edited Apr 2, 2020 at 5:49

answered Apr 2, 2020 at 5:45

Zhorov

3581 silver badge9 bronze badges

Thanks Zhorov will give this was a crack now

Stockburn
– Stockburn

2020-04-02 05:46:49 +00:00
Commented Apr 2, 2020 at 5:46
Zhorov, tested in a sandpit environment and works like a charm, thank you.

Stockburn
– Stockburn

2020-04-02 05:49:30 +00:00
Commented Apr 2, 2020 at 5:49

Add a comment |

Stockburn · Accepted Answer · 2020-04-02 05:46:20Z

0

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

answered Apr 2, 2020 at 5:46

Stockburn

5014 silver badges22 bronze badges

Add a comment |

Stack Exchange Network

T-SQL Update Random String Value in Column

2 Answers 2

Your Answer

Hot Network Questions

T-SQL Update Random String Value in Column

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions