-1

I have a table with list of text values that contain dates in any format types.

I need a TSQL query to remove the dates from each row in the table.

The date samples are below. What is the best way to remove these dates?

enter image description here

Sample table data:

enter image description here

closed as unclear what you're asking by Mikael Eriksson, Erik Darling, mustaccio, Philᵀᴹ, Andriy M Aug 9 '18 at 9:48

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

I'd probably agree with the comments that suggest solving the problem in an external program that has better string comparisons and manipulations, but this looked like a fun little challenge using TSQL.

The following solution is a brute force, fuzzy matched, non-efficient attempt to satisfy the question's requirements.


--Demo set up
--This is your sample data table 
--I added an additional column called TypeOfUpdate that I use
--to document the pattern that matched the update
Declare @StrTable table (TextCol varchar(100),TypeOfUpdate varchar(30))
insert into @StrTable(TextCol) values('67185019800138111td.2018.06.29.csv.asc.18.07.01_23.05.csv')  --[yyyyMMdd]
insert into @StrTable(TextCol) values('FX Spots Forwards Summary2_29-Jun-2018.csv')                 --[ddMMMyyyy]
insert into @StrTable(TextCol) values('QUINTIL_IRS_Open_20180629')                                  --[yyyyMMdd]
insert into @StrTable(TextCol) values('Share Register_290618.pdf')                                  --[ddMMyy]
insert into @StrTable(TextCol) values('PositionFileAP10180629.pdf')                                 --[yyMMdd]
insert into @StrTable(TextCol) values('HereItIs21July2018.csv')                                     --no format found

-----------------------

--The solution
--Create a date dimenison table with various columns for
--desired formats
--Adjust the StartDate as needed
DECLARE @StartDate DATE = '20180101'
    ,@NumberOfYears INT = 1;
DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

--Load the #DateDimWithFormats temporary table
drop table if exists #DateDimWithFormats
;WITH DateDim
AS (
    SELECT d
    FROM (
        SELECT d = DATEADD(DAY, rn - 1, @StartDate)
        FROM (
            SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) rn = ROW_NUMBER() OVER (
                    ORDER BY s1.[object_id]
                    )
            FROM sys.all_objects AS s1
            CROSS JOIN sys.all_objects AS s2
            -- on my system this would support > 5 million days
            ORDER BY s1.[object_id]
            ) AS x
        ) AS y
    )
    ,DateDimWithFormats
AS (
    SELECT *
        ,convert(char(8),FORMAT(d, 'yyyyMMdd', 'en-US')) AS [yyyyMMdd]
        ,FORMAT(d, 'yyyy-MM-dd', 'en-US') AS [yyyy-MM-dd]
        ,FORMAT(d, 'yyyy/MM/dd', 'en-US') AS [yyyy/MM/dd]
        ,FORMAT(d, 'yyyy.MM.dd', 'en-US') AS [yyyy.MM.dd]
        ,FORMAT(d, 'ddMMyy', 'en-US') AS [ddMMyy]
        ,FORMAT(d, 'MMddyyyy', 'en-US') AS [MMddyyyy]
        ,FORMAT(d, 'MMyyyydd', 'en-US') AS [MMyyyydd]
        ,FORMAT(d, 'yyMMdd', 'en-US') AS [yyMMdd]
        ,FORMAT(d, 'yy.MM.dd', 'en-US') AS [yy.MM.dd]
        ,FORMAT(d, 'dd-MMM-yyyy', 'en-US') AS [dd-MMM-yyyy]
        ,FORMAT(d, 'dd MMM yyyy', 'en-US') AS [dd MMM yyyy]
        ,FORMAT(d, 'ddMMMyyyy', 'en-US') AS [ddMMMyyyy]
    FROM DateDim
    )
SELECT * into #DateDimWithFormats FROM DateDimWithFormats

SELECT TextCol as TextColBeforeUpdate,TypeOfUpdate
FROM @StrTable

--Repeat the update for each unique format

--[yyyy.MM.dd]
UPDATE st
SET st.TextCol = replace(TextCol, d.[yyyy.MM.dd], '')
    ,st.TypeOfUpdate = '[yyyy.MM.dd]'
FROM @StrTable st
CROSS APPLY #DateDimWithFormats d
WHERE st.TextCol <> replace(TextCol, d.[yyyy.MM.dd], '')

--[dd-MMM-yyyy]
UPDATE st
SET st.TextCol = replace(TextCol, d.[dd-MMM-yyyy], '')
    ,st.TypeOfUpdate = '[dd-MMM-yyyy]'
FROM @StrTable st
CROSS APPLY #DateDimWithFormats d
WHERE st.TextCol <> replace(TextCol, d.[dd-MMM-yyyy], '')

--[yyyyMMdd]
UPDATE st
SET st.TextCol = replace(TextCol, d.[yyyyMMdd], '')
    ,st.TypeOfUpdate = '[yyyyMMdd]'
FROM @StrTable st
CROSS APPLY #DateDimWithFormats d
WHERE st.TextCol <> replace(TextCol, d.[yyyyMMdd], '')

--[yyyy-MM-dd]
UPDATE st
SET st.TextCol = replace(TextCol, d.[yyyy-MM-dd], '')
    ,st.TypeOfUpdate = '[yyyy-MM-dd]'
FROM @StrTable st
CROSS APPLY #DateDimWithFormats d
WHERE st.TextCol <> replace(TextCol, d.[yyyy-MM-dd], '')

--[yyyy/MM/dd]
UPDATE st
SET st.TextCol = replace(TextCol, d.[yyyy/MM/dd], '')
    ,st.TypeOfUpdate = '[yyyy/MM/dd]'
FROM @StrTable st
CROSS APPLY #DateDimWithFormats d
WHERE st.TextCol <> replace(TextCol, d.[yyyy/MM/dd], '')

--[ddMMMyyyy]
UPDATE st
SET st.TextCol = replace(TextCol, d.[ddMMMyyyy], '')
    ,st.TypeOfUpdate = '[ddMMMyyyy]'
FROM @StrTable st
CROSS APPLY #DateDimWithFormats d
WHERE st.TextCol <> replace(TextCol, d.[ddMMMyyyy], '')

--[ddMMyy]
UPDATE st
SET st.TextCol = replace(TextCol, d.[ddMMyy], '')
    ,st.TypeOfUpdate = '[ddMMyy]'
FROM @StrTable st
CROSS APPLY #DateDimWithFormats d
WHERE st.TextCol <> replace(TextCol, d.[ddMMyy], '')

--[yyMMdd]
UPDATE st
SET st.TextCol = replace(TextCol, d.[yyMMdd], '')
    ,st.TypeOfUpdate = '[yyMMdd]'
FROM @StrTable st
CROSS APPLY #DateDimWithFormats d
WHERE st.TextCol <> replace(TextCol, d.[yyMMdd], '')

--Verify the results
SELECT TextCol as TextColAfterUpdate,TypeOfUpdate
FROM @StrTable

| TextColBeforeUpdate                                       | TypeOfUpdate |
|-----------------------------------------------------------|--------------|
| 67185019800138111td.2018.06.29.csv.asc.18.07.01_23.05.csv | NULL         |
| FX Spots Forwards Summary2_29-Jun-2018.csv                | NULL         |
| QUINTIL_IRS_Open_20180629                                 | NULL         |
| Share Register_290618.pdf                                 | NULL         |
| PositionFileAP10180629.pdf                                | NULL         |
| HereItIs21July2018.csv                                    | NULL         |

| TextColAfterUpdate                              | TypeOfUpdate  |
|-------------------------------------------------|---------------|
| 67185019800138111td..csv.asc.18.07.01_23.05.csv | [yyyy.MM.dd]  |
| FX Spots Forwards Summary2_.csv                 | [dd-MMM-yyyy] |
| QUINTIL_IRS_Open_                               | [yyyyMMdd]    |
| Share Register_.pdf                             | [ddMMyy]      |
| PositionFileAP10.pdf                            | [yyMMdd]      |
| HereItIs21July2018.csv                          | NULL          |
  • Looks good but unfortunately we are still on SQL 2008R2. Upgrading later in year but not soon enough unfortunately. Will look to see how I can use C# and Regex – Kevin Aug 8 '18 at 17:51
1

Community wiki answer - add your contribution by editing:

I would strongly suggest using an external program (that has many more options such as regex for string manipulation) to make this change. T-SQL has relatively poor string-manipulation facilities.

At the very least I would use SSIS with a C# Script task to handle this.

Also, you should be careful to limit matches to actual date values (for instance, if you look for strings containing 6 consecutive digits, then "444400" could be a match - but wouldn't be a valid date in any of the formats you list).

Be aware that there could be legitimate non-date data that might match a seemingly valid date string: "20101010" could be "October 10, 2010", but it might something else as well; similarly, in the first row of your sample data, both "2018.06.29" and "18.07.01" could be dates - for that matter, "18.07.01_23.05" could be a date with a time.

Not the answer you're looking for? Browse other questions tagged or ask your own question.