how to update a column to replace the end of a pattern (SOLVED)

Question

how to update a column to replace the end of a pattern (SOLVED)

ideacipher

SSC Veteran

Points: 219
More actions
January 8, 2021 at 6:14 am

#3828852
I'm new to the forum and have a quick question.
Sample data set from "SELECT path from invoices"
temp/a/g.txt
test/b/t-t.txt
windows/c/45_4.txt

Is there a way native to SQL to update the above results to be:
temp/a/this.bat
test/b/this.bat
windows/c/this.bat
- This topic was modified 4 years, 8 months ago by ideacipher.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

It's not pretty, or fast, but it works:

SELECT
    dat.pth
,   path2 = STUFF(dat.pth, calc.pos2, calc.pos1 - calc.pos2, 'this')
FROM
(
    VALUES ('temp/a/g.txt')
    ,      ('test/b/t-t.txt')
    ,      ('windows/c/45_4.txt')
    ,      ('ksjdch/sdgsfdg/sgsdg/sdsd/sdgsdgs/fred.longextension')
) dat (pth)
    CROSS APPLY
(SELECT rev = REVERSE(dat.pth), lgth = LEN(pth)) r1
    CROSS APPLY
(
    SELECT
        pos1 = r1.lgth - CHARINDEX('.', r1.rev) + 1
    ,   pos2 = r1.lgth - CHARINDEX('/', r1.rev) + 2
) calc;

ScottPletcher SSC Guru Points: 101200 More actions · Answer 2

SELECT 
    path AS original_path,
    LEFT(path, LEN(path) - CHARINDEX('/', REVERSE(path)) + 1) + 'this.bat'
FROM
(
    VALUES ('temp/a/g.txt')
    ,      ('test/b/t-t.txt')
    ,      ('windows/c/45_4.txt')
    ,      ('ksjdch/sdgsfdg/sgsdg/sdsd/sdgsdgs/fred.longextension')
) AS invoices (path)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Blimey, I think I need to stop posting and see a doctor! For some reason, I thought that there was a need to maintain the original file extension.

ideacipher SSC Veteran Points: 219 More actions · Answer 4

Thanks for the replies. This works like a charm.

ScottPletcher wrote:

SELECT 
    path AS original_path,
    LEFT(path, LEN(path) - CHARINDEX('/', REVERSE(path)) + 1) + 'this.bat'
FROM
(
    VALUES ('temp/a/g.txt')
    ,      ('test/b/t-t.txt')
    ,      ('windows/c/45_4.txt')
    ,      ('ksjdch/sdgsfdg/sgsdg/sdsd/sdgsdgs/fred.longextension')
) AS invoices (path)