Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

T-SQL script to extract date from string and delete

Posted on 2013-11-11
6
Medium Priority
?
972 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
5 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

824 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