如何使用SQL正则函数提取非JSON格式字符串中的CODE字段值

Got it, let's figure out how to pull the M-1012 value (from the Code=M-1012 pair) from your unstructured string:

{Type=connect List, Value = Non Billable ,Code=M-1012,Listitemid= xyshdjf}

Since different SQL dialects use slightly different regex syntax, I'll cover the most common database systems below:

MySQL/MariaDB #

Use the REGEXP_SUBSTR function to target the Code= prefix and capture everything until the next comma (our value separator):

SELECT REGEXP_SUBSTR(
    '{Type=connect List, Value = Non Billable ,Code=M-1012,Listitemid= xyshdjf}',
    'Code=([^,]+)',
    1,
    1,
    'c',
    1
) AS code_value;

• Quick breakdown:
  • 'Code=([^,]+)': Matches Code= then captures all characters that aren't a comma (that's our target value)
  • The final 1 parameter tells the function to return the first captured group (the part inside parentheses)

PostgreSQL #

PostgreSQL offers two straightforward options:

Option 1: Use REGEXP_MATCHES (returns an array, so we pick the first element) #

SELECT (REGEXP_MATCHES(
    '{Type=connect List, Value = Non Billable ,Code=M-1012,Listitemid= xyshdjf}',
    'Code=([^,]+)'
))[1] AS code_value;

Option 2: Use REGEXP_REPLACE to strip all extra text #

SELECT REGEXP_REPLACE(
    '{Type=connect List, Value = Non Billable ,Code=M-1012,Listitemid= xyshdjf}',
    '.*Code=([^,]+).*',
    '\1'
) AS code_value;

SQL Server #

SQL Server doesn't have a native regex extract function, but we can combine SUBSTRING and PATINDEX to get the job done:

SELECT SUBSTRING(
    '{Type=connect List, Value = Non Billable ,Code=M-1012,Listitemid= xyshdjf}',
    PATINDEX('%Code=%', '{Type=connect List, Value = Non Billable ,Code=M-1012,Listitemid= xyshdjf}') + 5,
    CHARINDEX(',', '{Type=connect List, Value = Non Billable ,Code=M-1012,Listitemid= xyshdjf}', PATINDEX('%Code=%', '{Type=connect List, Value = Non Billable ,Code=M-1012,Listitemid= xyshdjf}')) 
    - (PATINDEX('%Code=%', '{Type=connect List, Value = Non Billable ,Code=M-1012,Listitemid= xyshdjf}') + 5)
) AS code_value;

Note: If you're on SQL Server 2017+, you can also use REGEXP_SUBSTR if your compatibility level is set correctly.

Oracle #

Oracle's REGEXP_SUBSTR works similarly to MySQL, with a minor syntax tweak:

SELECT REGEXP_SUBSTR(
    '{Type=connect List, Value = Non Billable ,Code=M-1012,Listitemid= xyshdjf}',
    'Code=([^,]+)',
    1,
    1,
    'i',
    1
) AS code_value
FROM dual;

All these examples will return M-1012 as the result. The core idea is targeting the Code= prefix and capturing everything until the next comma (the separator between key-value pairs in your string).

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