How to get the first letters form the string in mysql?

Question

I have a table called leaves

id    leave_name     status

 1   Casual Leave     0

 2   Sick Leave       0

I need to get the get the first letters from leave name like CL, SL

I tried LEFT() but the string length is not pre-defined.

Answer 1

There is an obvious edge case here, which is what should happen if the leave name should have more than two terms. I assume that you only want the initials from the first two words, regardless of how many appear.

SELECT
    CASE WHEN INSTR(leave_name, ' ') > 0 THEN
         CONCAT(LEFT(leave_name, 1),
                SUBSTRING(leave_name, INSTR(leave_name, ' ') + 1, 1))
         ELSE LEFT(leave_name, 1) END AS initials
FROM yourTable;

I also added coverage for an edge case when only one word be present. In this case, we just take the single initial of that word.

Demo