Need help with query. - SQL Server Forums

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)

Need help with query.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2008-10-07 : 11:37:59

Hi,

I have a dir_inf column which contain datetime, size, and backup file name. How can I extract each of them out.
Please see desired results below. Any helps would greatly appreciate.

DROP TABLE #Dir
GO
CREATE TABLE #Dir
(
dir_info VARCHAR(1000) NULL
)
GO

INSERT #Dir VALUES ('10/06/2008 08:14 PM 296448 QNXT_PLANDATA_MI_REB_Diff.bak')
INSERT #Dir VALUES ('10/06/2008 10:14 PM 864512 QNXT_PLANDATA_MI_REB_Diff.bak')
INSERT #Dir VALUES ('10/06/2008 11:14 AM 986414 QNXT_PLANDATA_MI_REB_Diff.bak')
GO

SELECT *
FROM #Dir
GO

dir_info
---------------------------------------------------------------------
10/06/2008 08:14 PM 296448 QNXT_PLANDATA_MI_REB_Diff.bak
10/06/2008 10:14 PM 864512 QNXT_PLANDATA_MI_REB_Diff.bak
10/06/2008 11:14 AM 986414 QNXT_PLANDATA_MI_REB_Diff.bak

-- Result want:
bckup_dt size bkup_file_name
----------------------- ------ --------------
10/06/2008 08:14 PM 296448 PLANDATA_MI_Diff.bak
10/06/2008 10:14 PM 864512 PLANDATA_MI_Diff.bak
10/06/2008 11:14 AM 986414 PLANDATA_MI_Diff.bak

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-07 : 11:49:13

Well if your columns have fixed length you could use the substring function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 13:39:15

if data format in column always follows this pattern, you may something like

SELECT  
LEFT(dir_info,PATINDEX('%[0-9] _M%'dir_info)+3) AS bckup_dt,
SUBSTRING(dir_info,PATINDEX('%[0-9] _M%'dir_info)+5,LEN(dir_ifo)-CHARINDEX(' ',REVERSE(dir_info))-(PATINDEX('%[0-9] _M%'dir_info)+4)) AS size,
REVERSE(LEFT(REVERSE(dir_info),CHARINDEX(' ',REVERSE(dir_info))-1)) AS bkup_filename
FROM Table

EDIT: corrected length for size col

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 14:04:30

visakh, in the size parsing script, you still have to subtract the length of the datepart.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 14:15:47