Solved

T-SQL script to extract date from string and delete

Posted on 2013-11-11
6
862 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 13

Assisted Solution

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

WHERE COL1 IS YOUR FIELD
0
 
LVL 1

Author Comment

by:cynx
ID: 39640857
Thanks, I used charindex and patindex to achieve the results.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 350 total points
ID: 39640888
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 49

Expert Comment

by:PortletPaul
ID: 39640889
oh, well too late, sorry.
0
 
LVL 1

Author Comment

by:cynx
ID: 39643819
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

728 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