Manipulating initial string for custom output

Databases
1

Hi there, I need your appreciated help.

This is the initial string in table MySQL:


XY00138427

Manipulate the string:


SELECT CONCAT(
           LEFT('XY00138427',4) ,'-1-'
         , MID('XY00138427',6,2),'0'
         , RIGHT('XY00138427',3)
                       ) _NewStringS;

For this output:


XY00-1-380427

But I have this problems:

  1. when the first four characters of the initial string is different from XY00 I need insert -2- in the manipulate string;
  2. when the characters number 5 of the initial string is equal to 8 I need insert -3- in the manipulate string;

Example 1 (the first four characters of the initial string is different from XY00):


XY50138427

Output:


XY50-2-380427

Example 2 (the characters number 5 of the initial string is equal to 8):


XY00800953

Output:


XY00-3-800953

Can you help me?
Thanks in advance

2

I’m sorry, but I have new problem… this code working:

SELECT CONCAT(
         LEFT('XY00138427',4) 
       , CASE WHEN MID('XY00138427',5,1) = '8'
              THEN '-3-'
              WHEN LEFT('XY00138427',4) = 'XY00'
              THEN '-1-'
              ELSE '-2-' END
         , MID('XY00138427',6,2),'0'
         , RIGHT('XY00138427',3)
                       ) _NewStringS;

When execute in the table MySQL, I have this error:
[Err] 1271 - Illegal mix of collations for operation ‘concat’

SELECT CONCAT(
           LEFT(`myInitialString`,4)
         , CASE WHEN MID(`myInitialString`,5,1) = '8' 
                THEN '-3-' 
                WHEN LEFT(`myInitialString`,4) = 'XY00' 
                THEN '-1-'
                ELSE '-2-' END
         , MID(`myInitialString`,6,2), '0'
         , RIGHT(`myInitialString`,3)
                       ) _NewStringS FROM tbl_1;
3

allow me to introduce you to the CASE expression –

SELECT CONCAT(
         LEFT('XY00138427',4) 
       , [COLOR="Blue"]CASE WHEN MID('XY00138427',5,1) = '8'
              THEN '-3-'
              WHEN LEFT('XY00138427',4) = 'XY00'
              THEN '-1-'
              ELSE '-2-' END[/COLOR]
         , MID('XY00138427',6,2),'0'
         , RIGHT('XY00138427',3)
                       ) _NewStringS;
4

Nice to meet you, thanks so much!