MySQL split string for INSERT-CodePudding

I have a string (long 17) like "AAAAAAAAAAAAAAAAA" and i need to write it in a varchar(21) field with this format "AAAA-AAAA-AAAA-AAAA-A".

For some reason it must be formatted in the INSERT statement like:

INSERT INTO table (field) VALUES (  FORMATED("AAAAAAAAAAAAAAAAA") )

I can't find the way to insert the delimiter every 4 chars, i will apreciete your help if you know how to.

Welcho

CodePudding user response：

You can create the custom function formatted() as shown below:

create function formatted(n varchar(17)) 
returns varchar(21)
begin
return concat(
  substring(n, 1, 4),
  '-',
  substring(n, 5, 4),
  '-',
  substring(n, 9, 4),
  '-',
  substring(n, 13, 4),
  '-',
  substring(n, 17, 1)
  );
end;
//

Then, it's matter of calling it while inserting the row. For example:

insert into t (f) values (formatted('12345678901234567'));

Result:

 f                     
 --------------------- 
 1234-5678-9012-3456-7

See running example at db<>fiddle.

Note: Of course you'll need to add basic validation to the function in case the parameter is shorter or longer than 17 characters. That's easy to add.