SQL查询：基于特定关键词拆分文本字段为多列

Hey there! I totally get why fixed-position functions like SUBSTRING or LEN aren't working here—when the keywords are in different spots per record, those tools just can't keep up. The fix is to use pattern matching or targeted string parsing that finds the keywords first, then grabs the values right after them. Let's walk through solutions for the most common databases, since syntax varies a bit:

Modern Databases (With Regex Support) #

Regex is the cleanest approach here because it lets you directly pattern-match the keywords and their associated values, regardless of where they sit in the text.

SQL Server 2017+ #

SELECT
  -- Extract numeric Warning Code value
  TRIM(REGEXP_REPLACE(YourTextField, '.*Warning Code\s*(\d+).*', '$1')) AS WarningCode,
  -- Extract Message content (captures text between "Message" and "Severity")
  TRIM(REGEXP_REPLACE(YourTextField, '.*Message\s*(.*?)\s*Severity.*', '$1')) AS Message,
  -- Extract Severity level
  TRIM(REGEXP_REPLACE(YourTextField, '.*Severity\s*(\w+).*', '$1')) AS Severity
FROM YourTable;

• The .* matches any characters before/after the keyword
• \s* accounts for any spaces between the keyword and its value
• (...) captures the value we want, which we reference with $1
• .*? uses non-greedy matching to stop at the next keyword (Severity) instead of the end of the string

MySQL 8.0+ #

SELECT
  TRIM(REGEXP_SUBSTR(YourTextField, 'Warning Code\\s*(\\d+)', 1, 1, 'c', 1)) AS WarningCode,
  TRIM(REGEXP_SUBSTR(YourTextField, 'Message\\s*(.*?)\\s*Severity', 1, 1, 'c', 1)) AS Message,
  TRIM(REGEXP_SUBSTR(YourTextField, 'Severity\\s*(\\w+)', 1, 1, 'c', 1)) AS Severity
FROM YourTable;

• REGEXP_SUBSTR directly extracts the matched pattern
• The final 1 specifies we want the first captured group
• The 'c' flag makes the match case-insensitive (remove it if you need exact case matching)

PostgreSQL #

SELECT
  TRIM((REGEXP_MATCHES(YourTextField, 'Warning Code\s*(\d+)'))[1]) AS WarningCode,
  TRIM((REGEXP_MATCHES(YourTextField, 'Message\s*(.*?)\s*Severity'))[1]) AS Message,
  TRIM((REGEXP_MATCHES(YourTextField, 'Severity\s*(\w+)'))[1]) AS Severity
FROM YourTable;

• REGEXP_MATCHES returns an array of captured groups, so we use [1] to get the first (and only) value we need
• Alternatively, you can use SUBSTRING(YourTextField FROM 'pattern') for a more concise syntax

Older Databases (No Regex Support) #

If you're stuck with a database that doesn't support regex, you can use CHARINDEX to locate the keywords and calculate the correct substring length:

SQL Server Pre-2017 / Legacy Databases #

SELECT
  -- Extract Warning Code
  TRIM(
    SUBSTRING(
      YourTextField,
      CHARINDEX('Warning Code', YourTextField) + LEN('Warning Code'),
      ISNULL(
        CHARINDEX('Message', YourTextField) - (CHARINDEX('Warning Code', YourTextField) + LEN('Warning Code')),
        LEN(YourTextField) - (CHARINDEX('Warning Code', YourTextField) + LEN('Warning Code')) + 1
      )
    )
  ) AS WarningCode,
  -- Extract Message
  TRIM(
    SUBSTRING(
      YourTextField,
      CHARINDEX('Message', YourTextField) + LEN('Message'),
      ISNULL(
        CHARINDEX('Severity', YourTextField) - (CHARINDEX('Message', YourTextField) + LEN('Message')),
        LEN(YourTextField) - (CHARINDEX('Message', YourTextField) + LEN('Message')) + 1
      )
    )
  ) AS Message,
  -- Extract Severity
  TRIM(
    SUBSTRING(
      YourTextField,
      CHARINDEX('Severity', YourTextField) + LEN('Severity'),
      LEN(YourTextField) - (CHARINDEX('Severity', YourTextField) + LEN('Severity')) + 1
    )
  ) AS Severity
FROM YourTable;

• CHARINDEX finds the starting position of each keyword
• ISNULL handles cases where a keyword is the last item in the text (uses the rest of the string instead of looking for a next keyword)
• TRIM cleans up any extra spaces around the extracted values

Handling Edge Cases #

• If some records are missing a keyword, wrap the extraction in COALESCE to return a default value (e.g., COALESCE(..., 'N/A'))
• Adjust the regex patterns if your values contain special characters (e.g., replace \d+ with .+ if Warning Code can have letters)

内容的提问来源于stack exchange，提问作者nirmal prasad Acharya