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

there is no role of mysql; you can view data in multiple lines by html settings — xkeshav, Oct 05 '11 at 09:59
Please see [SQL Scripts - Does the equivalent of a #define exist?][1] for my final solution [1]: http://stackoverflow.com/questions/9238978/sql-scripts-does-the-equivalent-of-a-define-exist — Ed Heal, Feb 24 '12 at 17:18

score 13 · Accepted Answer · answered Feb 24 '12 at 00:21

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"

Tom Mac · Answer 2 · 2011-10-05T12:43:03.160

You could split it up into the front and back components of the postcode and then dump the whole lot into a UDF.

This will keep the ugliness in one place and means you'll only have to make changes to one block of code when/if Royal Mail decide to change the format of UK postcodes ;-)

Something like this should do the trick:

DELIMITER $$


CREATE FUNCTION `isValidUKPostcode`(candidate varchar(255)) RETURNS BOOLEAN       READS SQL DATA
BEGIN
    declare back varchar(3);
    declare front varchar(10);
    declare v_out boolean;

    set back = substr(candidate,-3);
    set front = substr(candidate,1,length(candidate)-3);
    set v_out = false;

    IF (back REGEXP '^[0-9][ABD-HJLNP-UW-Z]{2}$'= 1) THEN
      CASE 
      WHEN front REGEXP '^[A-PR-UWYZ][A-HK-Y]{0,1}[0-9]{1,2} $' = 1 THEN set v_out = true;
      WHEN front REGEXP '^[A-PR-UWYZ][0-9][A-HJKMPR-Y] $' = 1 THEN set v_out = true;
      WHEN front REGEXP '^[A-PR-UWYZ][A-HK-Y][0-9][ABEHMNPRV-Y] $' = 1 THEN set v_out = true;
      END CASE;
    END IF;

    return v_out;
END

Having long strings in MySql stored procedures

2 Answers2

Linked