Formatting the SQL query result based on a pattern

Question

I have got a table that contains deferent type of date formats:

########
DateTime
########
10/05/2015
11/05/2015
1/5/2015
01/5/2014

Now my question is that How can I select all the rows based on this pattern \d{2}/\d{2}/\d{4} the format the result with this pattern \d{4}/\d{2}/\{2}? The first one is dd/mm/yyyy and I would like the result be yyyy/mm/dd

Sql server doesn't support regex but some pattern matching so you can't do what you want with regex. Unless you use a CLR — Jorge Campos, Aug 13 '15 at 00:10
@JorgeCampos it seems SQL Server can support Regex http://stackoverflow.com/questions/8928378/using-regex-in-sql-server — student, Aug 13 '15 at 00:18
Like I said it is just pattern matching, you can't use it to do your replacing. — Jorge Campos, Aug 13 '15 at 00:20

M.Ali · Accepted Answer · 2015-08-13T00:24:44.817

Test Data

DECLARE @TABLE TABLE (Dates VARCHAR(20))
INSERT INTO @TABLE VALUES 
('10/05/2015'),
('11/05/2015'),
('1/5/2015'),
('01/5/2014')

Query

The following query will convert all the values to proper sql server date data type.

SELECT CONVERT(DATE,
       RIGHT('0000' + PARSENAME(REPLACE(Dates , '/','.'),1),4)
     + RIGHT('00'   + PARSENAME(REPLACE(Dates , '/','.'),2),2)
     + RIGHT('00'   + PARSENAME(REPLACE(Dates , '/','.'),3),2)
      )
FROM @TABLE

Result

Once you have got the values in well-formatted sql server date type, you can extend the query to get the required output yyyy/mm/dd by doing the following:

SELECT  CONVERT(VARCHAR(10), 
      CONVERT(DATE,
       RIGHT('0000' + PARSENAME(REPLACE(Dates , '/','.'),1),4)
     + RIGHT('00'   + PARSENAME(REPLACE(Dates , '/','.'),2),2)
     + RIGHT('00'   + PARSENAME(REPLACE(Dates , '/','.'),3),2)
      ), 111)
FROM @TABLE

Result

score 0 · Answer 2 · answered Aug 13 '15 at 00:14

0

In your case I you don't need to use regular expressions. A simple LIKE should suffice.

... WHERE DateTime LIKE "__/__/____" ...

answered Aug 13 '15 at 00:14

Hazzit

6,782
1
27
46

Formatting the SQL query result based on a pattern

2 Answers2

Test Data

Query

Result

Result