Solved

T-SQL script to extract date from string and delete

Posted on 2013-11-11
6
712 Views
Last Modified: 2013-11-21
Hi Guys,

I have a column in database for 'FileName'. Possible file names in this column are as below;

FileXX (includes XX_2010_1_28_TO_2010_2_17.xml)
SSDSX_8_1_TO_2009_10_7.xml
XSDSX (includes ZZZZZ_2013_1_1_TO_2013_10_9-3.xml)
SXSS_2010_3_21.xml

I have to delete all records which have Bold date before 9th October 2013.

For text string with TO, date part after TO is to be considered
For text string with TO, but have a dash, the numeric value after dash can be ignored
For text string with single date, the date part has to be considered

How can I proceed to build such script?
0
Comment
Question by:cynx
  • 2
  • 2
6 Comments
 
LVL 13

Assisted Solution

by:Jesus Rodriguez
Jesus Rodriguez earned 150 total points
Comment Utility
SELECT SUBSTRING(COL1,CHARINDEX('TO_',COL1),LEN(COL1)-CHARINDEX('.XML',COL1))

WHERE COL1 IS YOUR FIELD
0
 
LVL 1

Author Comment

by:cynx
Comment Utility
Thanks, I used charindex and patindex to achieve the results.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 350 total points
Comment Utility
Here are 2 query variants, the top one is more conservative does not attempt to convert to date, it may pay to trial this. If it appears you are always assured of something that can be treated as date then include that conversion. Note I am using 2 cross applys. These are just so convenient for this as the results of one cross apply are available to the next so the number of function calls in the code are reduced and it becomes easier to read.
SELECT
        filename
      , fdate_str
     -- , convert(date,fdate_str,120) as fdate
FROM YourTable
CROSS APPLY ( SELECT
                     charindex('TO_',filename)
                   , charindex('_',filename)
                   , charindex('.xml',filename)
                   , patindex('%-[0-9]%',filename)
             ) AS ca1 (pos_t, pos_u, pos_x, pos_d)
CROSS APPLY ( SELECT replace(
                               CASE WHEN pos_t = 0 THEN substring(filename,pos_u + 1,pos_x -1 -pos_u)
                                    WHEN pos_t > 0 AND pos_d > 0 THEN substring(filename, pos_t + 3, pos_d -3 -pos_t)
                                    ELSE substring(filename,pos_t + 3, pos_x -3 -pos_t)
                               END
                             , '_' ,'-')
             ) AS ca2 (fdate_str)
;
	
SELECT
        filename
      , fdate
FROM YourTable
CROSS APPLY ( SELECT
                     charindex('TO_',filename)
                   , charindex('_',filename)
                   , charindex('.xml',filename)
                   , patindex('%-[0-9]%',filename)
             ) AS ca1 (pos_t, pos_u, pos_x, pos_d)
CROSS APPLY ( SELECT convert(date, replace(
                               CASE WHEN pos_t = 0 THEN substring(filename,pos_u + 1,pos_x -1 -pos_u)
                                    WHEN pos_t > 0 AND pos_d > 0 THEN substring(filename, pos_t + 3, pos_d -3 -pos_t)
                                    ELSE substring(filename,pos_t + 3, pos_x -3 -pos_t)
                               END
                             , '_' ,'-'), 120)
             ) AS ca2 (fdate)

Open in new window

I extended the test data a bit to look for single and double digit days in the filename, with these results:
|                                             FILENAME |  FDATE_STR |      FDATE |
|------------------------------------------------------|------------|------------|
|       FileXX (includes XX_2010_1_28_TO_2010_2_7.xml) |   2010-2-7 | 2010-02-07 |
|                           SSDSX_8_1_TO_2009_10_7.xml |  2009-10-7 | 2009-10-07 |
| XSDSX (includes ZZZZZ_2013_1_1_TO_2013_10_19-13.xml) | 2013-10-19 | 2013-10-19 |
|                                    SXSS_2010_3_2.xml |   2010-3-2 | 2010-03-02 |
|      FileXX (includes XX_2010_1_28_TO_2010_2_17.xml) |  2010-2-17 | 2010-02-17 |
|                           SSDSX_8_1_TO_2009_10_7.xml |  2009-10-7 | 2009-10-07 |
|   XSDSX (includes ZZZZZ_2013_1_1_TO_2013_10_9-3.xml) |  2013-10-9 | 2013-10-09 |
|                                   SXSS_2010_3_21.xml |  2010-3-21 | 2010-03-21 |

Open in new window

see: http://sqlfiddle.com/#!3/538b7/1
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
oh, well too late, sorry.
0
 
LVL 1

Author Comment

by:cynx
Comment Utility
Hi PortletPaul,

Thanks for your in depth solution. I will check Mods if there is a way to distribute points now.

Apologies, when I assigned points above was the only response.

Thanks
Mehul
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now