How to extract database name from string? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to extract database name from string?

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2008-10-17 : 11:31:08

Hi,

I have files names and need to extract the db name from backup file names. Details show below.
Thank you in advance.

Ex: NDXT_PLANDATA_TX_FULL_DB.bak --> NDXT_PLANDATA_TX only
NDXT_PLANDATA_TX_DB.bak --> NDXT_PLANDATA_TX
QNXT_PLANDATA_TX_Log.BAK --> QNXT_Plandata_tx

DROP TABLE [TestTable]
go
CREATE TABLE [dbo].[TestTable]
(
[bckup_dt] [varchar](25) NULL,
[size_dir] [int] NULL,
[bkup_file_name] [varchar](35) NULL,
[database_name] [varchar](45) NULL
)
GO
INSERT TestTable VALUES ('10/16/2008 11:28 PM', 18420848, 'NCDT_ProdDATA_OH_DB.BAK', NULL)
INSERT TestTable VALUES ('10/17/2008 07:30 AM', 9425484, 'QNTX_PLANDATA_OH_Log.BAK', NULL)

INSERT TestTable VALUES ('10/16/2008 11:36 PM', 328116, 'MyCeproc_UT_DB.BAK', NULL)
INSERT TestTable VALUES ('10/16/2008 11:32 PM', 416673, 'MyCeproc_UT_TPA_REB_DB.BAK', NULL)

INSERT TestTable VALUES ('10/17/2008 02:42 AM', 46305845, 'QNXT_ELIGPROC_WA_DB.BAK', NULL)
INSERT TestTable VALUES ('10/15/2008 02:40 AM', 63106755, 'QNXT_ELIGPROC_WA_FULL_DB.BAK', NULL)

INSERT TestTable VALUES ('10/16/2008 04:00 PM', 203248, 'MNXT_PLANDATA_TX_DIFF_DB.bak', NULL)
INSERT TestTable VALUES ('10/12/2008 09:36 PM', 3030369, 'NDXT_PLANDATA_TX_FULL_DB.BAK', NULL)
INSERT TestTable VALUES ('10/12/2008 09:36 PM', 3030369, 'NDXT_PLANDATA_TX_DB.BAK', NULL)
INSERT TestTable VALUES ('10/17/2008 07:30 AM', 2572046, 'QNXT_PLANDATA_TX_Log.BAK', NULL)
GO

SELECT *
FROM TestTable
GO

-- Result want:

bckup_dt size_dir bkup_file_name database_name
------------------------- ----------- ----------------------------------- ------------------
10/16/2008 11:28 PM 18420848 NCDT_ProdDATA_OH_DB.BAK NCDT_ProdDATA_OH
10/17/2008 07:30 AM 9425484 QNTX_PLANDATA_OH_Log.BAK QNTX_PLANDATA_OH
10/16/2008 11:36 PM 328116 MyCeproc_UT_DB.BAK MyCeproc_UT_DB
10/16/2008 11:32 PM 416673 MyCeproc_UT_TPA_REB_DB.BAK MyCeproc_UT_TPA_REB
10/17/2008 02:42 AM 46305845 QNXT_ELIGPROC_WA_DB.BAK QNXT_ELIGPROC_WA
10/15/2008 02:40 AM 63106755 QNXT_ELIGPROC_WA_FULL_DB.BAK QNXT_ELIGPROC_WA
10/16/2008 04:00 PM 203248 MNXT_PLANDATA_TX_DIFF_DB.bak MNXT_PLANDATA_TX
10/12/2008 09:36 PM 3030369 NDXT_PLANDATA_TX_FULL_DB.BAK NDXT_PLANDATA_TX
10/17/2008 07:30 AM 2572046 QNXT_PLANDATA_TX_Log.BAK QNXT_PLANDATA_TX

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-17 : 11:33:39

Database names always end with a state name right?

NguyenL71
Posting Yak Master

228 Posts

Posted - 2008-10-17 : 12:03:00

quote:
Originally posted by hanbingl

Database names always end with a state name right?

Not neccessary end with state name. Example:
bckup_dt size_dir bkup_file_name database_name
------------------------- ----------- ----------------------------------- -------------
10/16/2008 11:32 PM 416673 MyCeproc_UT_TPA_REB_DB.BAK NULL

I like see db like this. Thanks.
MyCeproc_UT_TPA_REB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 12:15:49

SELECT LEFT(PARSENAME(bkup_file_name,2),CASE WHEN PATINDEX('%_DB',PARSENAME(bkup_file_name,2))>0 THEN PATINDEX('%_DB',PARSENAME(bkup_file_name,2))-1 WHEN WHEN PATINDEX('%_LOG',PARSENAME(bkup_file_name,2))>0 THEN PATINDEX('%_LOG',PARSENAME(bkup_file_name,2))-1 ELSE LEN(PARSENAME(bkup_file_name,2)) END)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-18 : 08:40:36

If all dbnames has suffix _db or _log, then

SELECT left(bkup_file_name,len(bkup_file_name)-charindex('_',reverse(bkup_file_name)))
FROM TestTable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources