How to optimize multiple WHEN that are using differents static string?

Question

I have a table like that:

Id	Note	Client
1	Long note that have multiple lines Client: Name	null
2	Anoter note that have multiple lines Customer: Name	null
3	Third note that have multiple lines Client : Name line	null
4	Line: A Client : Name line	null
5	Line B B : Line Customer : Name line others	null

I want to extract client informations from the "Note" column, to put it on the "Client" column with only one SQL query.

I gave some example that represent the real thousand of line I have. I firstly made this query:

SELECT
    CASE
        WHEN posclient != 0 THEN SUBSTRING(
            Note,
            posclient + LENGTH('Client: '),
            LOCATE('\n', Note, posclient + LENGTH('Client: ')) - (posclient + LENGTH('Client: '))
        )
        WHEN poscustomer != 0 THEN SUBSTRING(
            Note,
            poscustomer + LENGTH('Customer : '),
            LOCATE('\n', Note, poscustomer + LENGTH('Customer : ')) - (poscustomer + LENGTH('Customer : '))
        )
        WHEN poscontact != 0 THEN SUBSTRING(
            Note,
            poscontact + LENGTH('Contact : '),
            LOCATE('\n', Note, poscontact + LENGTH('Contact : ')) - (poscontact + LENGTH('Contact : '))
        )
        ELSE ''
    END AS client,
    Id
FROM (SELECT Id, Note,
        LOCATE('Customer : ', Note) as poscustomer,
        LOCATE('Client: ', Note) as posclient,
        LOCATE('Contact : ', Note) as poscontact
    FROM myTable) x 
WHERE (poscustomer != 0 OR posclient != 0 OR poscontact != 0)

As there is duplicated code (the WHEN part), it's hard to read and not very fine if I have another way to add.

And I have: there is at least 6 ways to write the client. I want to optimize this part:

WHEN poscontact != 0 THEN SUBSTRING(
   Note,
   poscontact + LENGTH('Contact : '),
   LOCATE('\n', Note, poscontact + LENGTH('Contact : ')) - (poscontact + LENGTH('Contact : '))
)

To be "generic" but I don't know how as I'm not in programming language. In php, that would be easy, but I can't use programming language, I have to use only one query.

One thing that can be very helpful: the is only ONE way to write the client for each line. It will always contains only one.

How can I do?

Akina · Answer 1 · 2024-08-28T13:40:44.100

UPDATE test
SET Client = SUBSTRING_INDEX(Note, ': ', -1);

fiddle

The issue is if another line contains ": ", it's broken

There is too much to post all of them, but I'll edit my post and be sure I don't miss some specific one

According the values provided the query must be updated up to

UPDATE test
SET Client = SUBSTRING_INDEX(SUBSTRING_INDEX(Note, ': ', -1), CHAR(10), 1);

If your strings are Windows-style then replace the delimiter with CONCAT(CHAR(13), CHAR(10)).

fiddle

score 0 · Accepted Answer · answered Aug 28 '24 at 13:04

I found a way. By using the function proposed by Akina: SUBSTRING_INDEX, there is an easy way: find the key word, then use it as param.

DB Fiddle

The query:

UPDATE test SET Client = (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(Note, clientPath, -1), '\n', 1)
  FROM (SELECT Note,
       CASE
            WHEN LOCATE('Client: ', Note) != 0 THEN 'Client: '
            WHEN LOCATE('Client : ', Note) != 0 THEN 'Client : '
            WHEN LOCATE('Contact: ', Note) != 0 THEN 'Contact: '
            WHEN LOCATE('Contact : ', Note) != 0 THEN 'Contact : '
            WHEN LOCATE('Customer: ', Note) != 0 THEN 'Customer: '
            WHEN LOCATE('Customer : ', Note) != 0 THEN 'Customer : '
            ELSE ''
       END AS clientPath, Id
       FROM test) x
  WHERE clientPath != '' and test.id = x.id);

That make it easy to add new way: just add WHEN LOCATE (x, Note) != 0 THEN x.

How to optimize multiple WHEN that are using differents static string?

2 Answers2