Having long strings in MySql stored procedures

Question

Is there a way of enabling a long strings to be put onto multiple lines so that when viewed on screen or printed the code is easier to read?

Perhaps I could be clearer.

Have a stored procedure with lines like

   IF ((select post_code REGEXP '^([A-PR-UWYZ][A-HK-Y]{0,1}[0-9]{1,2} [0-9][ABD-HJLNP-UW-Z]{2})|([A-PR-UWYZ][0-9][A-HJKMPR-Y] [0-9][ABD-HJLNP-UW-Z]{2})|([A-PR-UWYZ][A-HK-Y][0-9][ABEHMNPRV-Y]) [0-9][ABD-HJLNP-UW-Z]{2})$') = 0)

Would like to be able to modify the string so that I can view it within 80 character width. Anybody got any ideas of how to do this.

PS: It is the regular expression for UK postcodes

Answer 1

For example,

-- a very long string in one block
set my_str = 'aaaabbbbcccc';

can be also written as

-- a very long string, as a concatenation of smaller parts
set my_str = 'aaaa' 'bbbb' 'cccc';

or even better

-- a very long string in a readable format
set my_str = 'aaaa'
             'bbbb'
             'cccc';

Note how the spaces and end of line between the a/b/c parts are not part of the string itself, because of the placement of quotes.

Also note that the string data here is concatenated by the parser, not at query execution time.

Writing something like:

-- this is broken
set my_str = 'aaaa 
              bbbb 
              cccc';

produces a different result.

See also http://dev.mysql.com/doc/refman/5.6/en/string-literals.html

Look for "Quoted strings placed next to each other are concatenated to a single string"