Solved

T-SQL script to extract date from string and delete

Posted on 2013-11-11
6
744 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
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 48

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 48

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

861 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

23 Experts available now in Live!

Get 1:1 Help Now