need help with the code

Question

Post reply

need help with the code

sql2k8

Hall of Fame

Points: 3455
More actions
July 14, 2011 at 9:18 pm

#389982

Hi,
Can any one help me please
I have a column called Drive in a table A
Contents of the table A are as described below.
Drive
------
K:\Backups\Differential\development.bak
-------------------------------------------------------------------
@Database=Test, @FileName=K:\BackupS\Differential\Test\Test_Diff_2007-07-12-220004.bak, @FileNumber=0, @Desc=Backup differential database of Reports on 7/12/2007 10:00:04 PM, @BackupName=Backup differential database of Test, @RetainDays=1, @Init=1, @Priority=0, @With=DIFFERENTIAL, @Logging=0, @Affinity=255, @MaxTransferSize=1048576, @Throttle=50, @BufferCount=20, @CompressionLevel=4, @OLRMAP=0
----------------------------------------------------------------------
I need to write a select statement which should give me output like this
Drive
-------------
K:\Backups\Differential\development.bak
-------------
K:\BackupS\Differential\Test\Test_Diff_2007-07-12-220004.bak
Both of the rows which i had described above are two rows in a single column.
Can any one please help me with the code.
Thanks in advance,
Thank You,

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

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

You can use SUBSTRING & CHARINDEX functions to retrieve the data.

The example is as below:

declare @aa nvarchar(100) = '@Database=Test, @FileName=K:\BackupS\Differential\Test\Test_Diff_2007-07-12-220004.bak, @FileNumber'

select SUBSTRING(@aa, Charindex('K:', @aa), charindex('.bak', @aa) - (Charindex('K:', @aa)-4))

It returns:

K:\BackupS\Differential\Test\Test_Diff_2007-07-12-220004.bak

Thanks

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

SQL2k8, if the above didn't help you, can you make that into a CREATE TABLE and INSERT statement for us, so we can see exactly what you're seeing?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Details are in the comments in the code...

--===== Create a test table. This is NOT a part of the solution.

CREATE TABLE TableA

(

Drive VARCHAR(1000)

)

;

--===== Populate the test table. Notice the different drive letters.

-- Again, this is NOT a part of the solution.

INSERT INTO TableA

SELECT 'K:\Backups\Differential\development.bak' UNION ALL

SELECT '@Database=Test, @FileName=K:\BackupS\Differential\Test\Test_Diff_2007-07-12-220004.bak, @FileNumber=0, @Desc=Backup differential database of Reports on 7/12/2007 10:00:04 PM, @BackupName=Backup differential database of Test, @RetainDays=1, @Init=1, @Priority=0, @With=DIFFERENTIAL, @Logging=0, @Affinity=255, @MaxTransferSize=1048576, @Throttle=50, @BufferCount=20, @CompressionLevel=4, @OLRMAP=0' UNION ALL

SELECT '@Database=Test, @FileName=Z:\BackupS\Differential\Test\Test_Diff_2007-07-13-001015.bak, @FileNumber=0, @Desc=Backup differential database of Reports on 7/12/2007 10:00:04 PM, @BackupName=Backup differential database of Test, @RetainDays=1, @Init=1, @Priority=0, @With=DIFFERENTIAL, @Logging=0, @Affinity=255, @MaxTransferSize=1048576, @Throttle=50, @BufferCount=20, @CompressionLevel=4, @OLRMAP=0'

;

--===== Making the terrible assumption that all backup files will have the extension of ".bak",

-- find the full drive:\path\filename.bak no matter what the drive letter is.

SELECT SUBSTRING(Drive,PATINDEX('%[A-Z]:\%',Drive),CHARINDEX('.bak',Drive)+4-PATINDEX('%[A-Z]:\%',Drive))

FROM TableA

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql2k8 Hall of Fame Points: 3455 More actions · Answer 4

sql2k8

Hall of Fame

Points: 3455

July 15, 2011 at 7:54 am

#1353435

Thank You very much. Your code works

sql2k8 Hall of Fame Points: 3455 More actions · Answer 5

sql2k8

Hall of Fame

Points: 3455

July 15, 2011 at 7:55 am

#1353437

the below code also works

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 6

sql2k8 (7/15/2011)
the below code also works

code please..

Thanks

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

Hardy21 (7/17/2011)
sql2k8 (7/15/2011)
the below code also works
code please..

It's a "newbie" forum mistake. His first post where the OP said "Thank You very much. Your code works " was in reference to your good code. His second post of "the below code also works " was in reference to my code because my code is physically below your code on this thread.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 8

Jeff Moden (7/18/2011)
Hardy21 (7/17/2011)
sql2k8 (7/15/2011)
the below code also works
code please..
It's a "newbie" forum mistake. His first post where the OP said "Thank You very much. Your code works " was in reference to your good code. His second post of "the below code also works " was in reference to my code because my code is physically below your code on this thread.

Great 🙂

Thanks

Jannie-186227 SSCrazy Points: 2319 More actions · Answer 9

Hi

Just a hint, if you are doing backup monitoring instead of using output from funny programs and try to format them.

Checkout msdb

backupset

backupmedia

backupmediafamily

Even if you use 3rd party apps, these tables still hold the info for the dates backed up, location and times.

It also populates that GUI that says, "last backup date"

Cheers

Jannie