SQL Question

I have a field that is stored as a string that is currently holding dates,

This field should be stored as yyyy/mm/dd but unfortunately a large number of data was entered as mm/dd/yyyy

I want to know if there is a way to query the field to find only the rows that are in the wrong format.

CodePudding user response：

You don't specify the RDBMS but on SQL Server at least you could use this to get (only) the wrong format rows:

SELECT * FROM borkeddatestable WHERE datecolumnname LIKE '__/%'

Since the underscore - '_' is a single-character wildcard this will get you all rows where there's two characters followed by a '/'

CodePudding user response：

Most RDBMS offer some sort of substring function so given only your incorrect strings will have / at character position 3 you can simply say

where substring(Datecolumn,3,1) = '/';

CodePudding user response：

For pattern matching on a string you want to use a LIKE statement like shown bellow:

CREATE TABLE dates (dates VARCHAR(10));

INSERT INTO dates
VALUES  ('2022/01/02')
        ,('2022/02/27')
        ,('06/17/2022')
        ,('2022/03/12')
        ,('07/15/2022')
        ,('07/20/2022')
        ,('2022/04/02')

SELECT d.dates
FROM dates d
WHERE d.dates LIKE '__/__/____'

Resuts Table

Update Incorrect Entries

UPDATE d
SET d.dates = RIGHT(d.dates, 4)   '/'   LEFT(d.dates, 2)   SUBSTRING(d.dates, 3, 3)
FROM dates d
WHERE d.dates LIKE '__/__/____'

DB Fiddle: SQL Server Example