求Teradata中提取注释字符串任意位置日期的SQL查询语句

Got it, let's tackle this problem of pulling dates out of comment strings—no matter where the date is placed in the text. Teradata's regex tools make this pretty straightforward once you target the right pattern.

Core Solution #

We’ll use two key Teradata functions to get the job done:

• REGEXP_SUBSTR: Locates and extracts the first substring that matches your DD-MM-YYYY date format, regardless of its position in the comment.
• TO_DATE: Converts the extracted string into a proper Teradata DATE type (not just a text string).

Example Query #

Assume you have a table named comment_records with a column comment_text holding your target strings. Here’s the full query:

SELECT
  comment_text,
  -- Extract the date string then convert to DATE format
  TO_DATE(
    REGEXP_SUBSTR(comment_text, '\d{2}-\d{2}-\d{4}'),
    'DD-MM-YYYY'
  ) AS extracted_date
FROM
  comment_records;

Regex Pattern Breakdown #

The regex \d{2}-\d{2}-\d{4} is built to match exactly your date structure:

• \d{2}: Two digits for the day
• -: The hyphen separator
• \d{2}: Two digits for the month
• -: Another hyphen separator
• \d{4}: Four digits for the year

Testing with Your Sample Inputs #

Run this query against your example comments, and you’ll get these results:

• Input: 'Drop table on 12-09-2010' → Output: DATE '2010-09-12'
• Input: '12-09-2010' → Output: DATE '2010-09-12'
• Input: 'Drop 12-09-2010' → Output: DATE '2010-09-12'

Edge Cases & Fallbacks #

1. Multiple Dates in One String: If a comment has more than one date, REGEXP_SUBSTR only returns the first match. To extract all dates, use REGEXP_SPLIT_TO_TABLE to split the string into separate rows for each date.
2. Older Teradata Versions: If you’re on a version before 14.10 (when regex functions launched), use STRPOS and SUBSTR as a fallback:

   SELECT
     comment_text,
     TO_DATE(
       SUBSTR(comment_text, STRPOS(comment_text, '-')-2, 10),
       'DD-MM-YYYY'
     ) AS extracted_date
   FROM
     comment_records
   WHERE
     STRPOS(comment_text, '-') > 2; -- Ensure we have a valid date starting point
   

Quick Note #

Double-check that the format string in TO_DATE matches your comment date structure. If your dates use slashes (/) instead of hyphens, just adjust both the regex and format string accordingly.

内容的提问来源于stack exchange，提问作者PRATZ123