CODEWITHSUNDEEP
mysqlstring
Ed Heal
Ed Heal

Reputation: 59997

Having long strings in MySql stored procedures

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

Upvotes: 5

Views: 12151

Answers (2)

Marc Alff
Marc Alff

Reputation: 8395

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"

Upvotes: 14

Tom Mac
Tom Mac

Reputation: 9853

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

Upvotes: 0

Related Questions