string selection

Question

string selection

mtz676

SSCrazy Eights

Points: 9212
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: 246962 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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Michael L John One Orange Chip Points: 27056 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: 101101 More actions · Answer 3

SELECT
    FilePath,
    LEFT(FilePath, LEN(FilePath) - CHARINDEX('\', REVERSE(FilePath)))
FROM #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".

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 4

mtz676

SSCrazy Eights

Points: 9212

November 3, 2020 at 4:59 pm

#3803957

that worked.thanks

mtz676 SSCrazy Eights Points: 9212 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: 246962 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Michael L John One Orange Chip Points: 27056 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: 9212 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.