Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-SQL script to extract date from string and delete

Posted on 2013-11-11
6
Medium Priority
?
931 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 600 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 1400 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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

715 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