How to parse a string and get the value after "=" character-CodePudding

So I have string that should contains "Object.Name" once in a row , if I see it ,I have to get the value after "=" character. If doesn't match it anywhere in the string i should move hardcoded value.

Here is example of the string:

Object.Name=ASDD||Product.Name=DSA
Product.Name=QWE||Object.Name=WSXS
Storage.Name=12345||Object.Name=WERR||Product.Name=QAZ

I know that I should use case for that but doesn't know how to proceed the string

case
    when (match the string ) then (value after the "=")
    else (hardcoded value)
end

CodePudding user response：

In Oracle, you can use:

SELECT value,
       CASE
       WHEN start_pos = 0
       THEN NULL
       ELSE SUBSTR(
              '||' || value || '||',
              start_pos   LENGTH('||Object.Name='),
              end_pos - start_pos - LENGTH('||Object.Name=')
            )
       END AS object_name
FROM   (
  SELECT value,
         INSTR(
           '||' || value || '||',
           '||Object.Name='
         ) AS start_pos,
         INSTR(
           '||' || value || '||',
           '||',
           INSTR('||' || value || '||', '||Object.Name=') LENGTH('||Object.Name=')
         ) AS end_pos
  FROM   table_name
)

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'Object.Name=ASDD||Product.Name=DSA' FROM DUAL UNION ALL
SELECT 'Product.Name=QWE||Object.Name=WSXS' FROM DUAL UNION ALL
SELECT 'Storage.Name=12345||Object.Name=WERR||Product.Name=QAZ' FROM DUAL;

Outputs:

VALUE OBJECT_NAME

Object.Name=ASDD||Product.Name=DSA ASDD

Product.Name=QWE||Object.Name=WSXS WSXS

Storage.Name=12345||Object.Name=WERR||Product.Name=QAZ WERR

VALUE	OBJECT_NAME
Object.Name=ASDD\|\|Product.Name=DSA	ASDD
Product.Name=QWE\|\|Object.Name=WSXS	WSXS
Storage.Name=12345\|\|Object.Name=WERR\|\|Product.Name=QAZ	WERR

db<>fiddle here

Since you changed the tags, in MySQL:

SELECT value,
       CASE
       WHEN start_pos = 0
       THEN NULL
       ELSE SUBSTRING(
              CONCAT('||', value, '||'),
              start_pos   LENGTH('||Object.Name='),
              end_pos - start_pos - LENGTH('||Object.Name=')
            )
       END AS object_name
FROM   (
  SELECT value,
         LOCATE(
           '||Object.Name=',
           CONCAT('||', value, '||')
         ) AS start_pos,
         LOCATE(
           '||',
           CONCAT('||', value, '||'),
           LOCATE('||Object.Name=', CONCAT('||', value, '||'))
               LENGTH('||Object.Name=')
         ) AS end_pos
  FROM   table_name
) t

db<>fiddle here