-1

Hi i am trying to use regex to detect a string which is basically a date format dd-MM-Y in where the month has a '0',and I want to select the string without the '0'.

i.e 13/03/2021 ----> 13/3/2021

however, if the month is 10,11,12, it will keep the full string without transforming it.

i.e 11/11/2020 ----> 11/11/2020

thanks

Adamtky
  • 33
  • 4
  • 1
    Regex alone isn't really the right tool for this. What language are you using with your regex and data here? – Tim Biegeleisen Mar 25 '21 at 03:43
  • @TimBiegeleisen Hi, I am asking this question for my colleague, he is actually using SQL trying to convert the date(in string format) to get rid of the 0. Here's the code he uses: CAST(REGEXP_REPLACE(CAST(Date AS STRING),'(?<=/)[0]','') AS STRING) – Adamtky Mar 25 '21 at 05:20
  • 1
    You should be using the built in text to date conversion function which your version of SQL _might_ have. That being said, what is your version of SQL (e.g. MySQL, SQL Server, Oracle, etc.) ? – Tim Biegeleisen Mar 25 '21 at 05:22
  • @TimBiegeleisen we actually use google BigQuery. I suppose is Standard SQL? – Adamtky Mar 25 '21 at 05:33
  • 2
    See here: https://stackoverflow.com/questions/44564887/string-to-date-in-bigquery – Tim Biegeleisen Mar 25 '21 at 05:38
  • @Adamtky please read [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers) – Vishal Singh Mar 26 '21 at 08:01

2 Answers2

2

This works in BigQuery.

REGEXP_REPLACE(CAST(Date AS STRING),'^(\d{2}\/)0(\d\/\d{4})$','\\1\\2')
Vishal Singh
  • 6,014
  • 2
  • 17
  • 33
0

The following regex should help you match the dates by omitting the zeroes at the beginning of days and/or months.

0?([\d]{1,2})\/0?([\d]{1,2})\/([\d]{4})

You can reproduce the date in the required format by concatenating the capture groups 1, 2, and 3 with the delimiter of your choice. For example

$newDate = $1 . '/' . $2 . '/' . $3
stunner.agk
  • 36
  • 3