string selection

Question

string selection

mtz676

SSCrazy Eights

Points: 9185
More actions
November 3, 2020 at 4:00 pm

#3803929

If my column contains these values.
A:\AABCD\EDFG\RFG\MyDB123.bak
A:\RFG\MyDB1123.bak
A:\AABCD\EDFG\MyDB1223.bak
How do I select only these in my output
A:\AABCD\EDFG\RFG
A:\RFG
A:\AABCD\EDFG
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

DROP TABLE IF EXISTS #Path;

CREATE TABLE #Path
(
    FilePath VARCHAR(200) NOT NULL
);

INSERT #Path
(
    FilePath
)
VALUES
('A:\AABCD\EDFG\RFG\MyDB123.bak')
,('A:\RFG\MyDB1123.bak')
,('A:\AABCD\EDFG\MyDB1223.bak');

SELECT p.FilePath
      ,LEFT(p.FilePath, CHARINDEX('MyDB', p.FilePath) - 2)
FROM #Path p;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Michael L John One Orange Chip Points: 27044 More actions · Answer 2

What are you trying to do?

A more complete description of the entire set of things you are trying to accomplish may help us help you with a better solution.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ScottPletcher SSC Guru Points: 100952 More actions · Answer 3

SELECT
    FilePath,
    LEFT(FilePath, LEN(FilePath) - CHARINDEX('\', REVERSE(FilePath)))
FROM #Path

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

mtz676 SSCrazy Eights Points: 9185 More actions · Answer 4

mtz676

SSCrazy Eights

Points: 9185

November 3, 2020 at 4:59 pm

#3803957

that worked.thanks

mtz676 SSCrazy Eights Points: 9185 More actions · Answer 5

I am trying to build an extract of backup files(full,diff,log) along with locations(without file names as in the query provided here) usingbackupset and backupmediafamily tables.(past 7 days of days should suffice)

Phil Parkin SSC Guru Points: 246723 More actions · Answer 6

mtz676 wrote:

that worked.thanks

And mine didn't? For the sample data you provided, I think it did; it's fast too.

If, as Scott no doubt correctly surmised, your actual data contains variations on MyDB, why did you not include some variations in your sample data?

Too lazy would be my guess, which is backed up by the fact that you were unprepared to include a set-up DDL and INSERT script.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Michael L John One Orange Chip Points: 27044 More actions · Answer 7

mtz676 wrote:

I am trying to build an extract of backup files(full,diff,log) along with locations(without file names as in the query provided here) usingbackupset and backupmediafamily tables.(past 7 days of days should suffice)

What are you going to do with this list? Will it be a daily report so you can see if a backup was successful? Something else?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

mtz676 SSCrazy Eights Points: 9185 More actions · Answer 8

Hello Michael and Phil and All

When I am stuck in a particular query in the bigger scheme of things I intend to describe only that part of the problem which I was able to resolve with your assistance. No question you guys would definitely give a better way of dealing with the problem at hand.

If I am entirely clueless about something I would absolutely explain everything start to finish.

Thanks for all the help rendered.