Converting MSSQL function to MySQL function

Question

I am trying to convert this user defined function (taken from a MSSQL) and tweak it so that I can run it MYSQL. I have made several attempts but all seem to error on the declare variable.

I am running the following version: 5.6.11-log - MySQL Community Server (GPL)

USE [DataGB]
GO
/****** Object:  UserDefinedFunction [dbo].[FullPostCodeFix]    Script Date: 11/20/2013      16:10:44 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE FUNCTION [dbo].[FullPostCodeFix] (@Postcode  VARCHAR(20))  
RETURNS VARCHAR(20)

BEGIN 
/*
Puts postcode into correct format if it is currently in any of the below formats
AB12AA
AB 12AA
AB1 2AA
AB 1 2AA
Returns @Postcode
*/

DECL ARE @District Varchar(50)
DECLARE @Remainder Varchar(50)
DECLARE @Sector Varchar(50)

SET @District=  CASE 
            WHEN LEN(@Postcode) - CHARINDEX(' ', REVERSE(@Postcode)) =     len(@Postcode) THEN SUBSTRING(@Postcode,1,(len(@Postcode) - 3))
            WHEN LEN(@Postcode) - CHARINDEX(' ', REVERSE(@Postcode)) < 3 THEN     SUBSTRING(@Postcode,1,(len(@Postcode) - 3))
            ELSE SUBSTRING(@Postcode, 0, LEN(@Postcode) - CHARINDEX(' ', REVERSE(@Postcode)) + 1)
        END


SET @District = dbo.PostcodeFix(@District)

SET @Remainder= RIGHT(@Postcode,3)

SET @Sector = @District +  ' ' + LEFT(@Remainder,1)

SET @Postcode = @District +  ' ' + @Remainder

RETURN @Postcode

END

My attempt at creating a MYSQL version is below:

CREATE FUNCTION FullPostCodeFix (Postcode  VARCHAR(20))  
RETURNS VARCHAR(20)

BEGIN 


DECLARE District Varchar(50)
DECLARE Remainder Varchar(50)
DECLARE Sector Varchar(50)

SET District=  CASE 
            WHEN LEN(Postcode) - CHARINDEX(' ', REVERSE(Postcode)) =     len(Postcode) THEN SUBSTRING(Postcode,1,(len(Postcode) - 3))
            WHEN LEN(Postcode) - CHARINDEX(' ', REVERSE(Postcode)) < 3 THEN     SUBSTRING(Postcode,1,(len(Postcode) - 3))
            ELSE SUBSTRING(Postcode, 0, LEN(Postcode) - CHARINDEX(' ',     REVERSE(Postcode)) + 1)
        END

SET District = dbo.PostcodeFix(District)

SET Remainder= RIGHT(Postcode,3)

SET Sector = District +  ' ' + LEFT(Remainder,1)

SET Postcode = District +  ' ' + Remainder

RETURN Postcode

END

The error that I get is as follows:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE Remainder Varchar(50) DECLARE Sector Varchar(50)

There is another function called from within the function "FullPostCodeFix". This is my attempt:

DELIMITER $$
CREATE FUNCTION PostCodeFix (strDistrict  VARCHAR(20))  
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN 
    DECLARE intASCII INTEGER;
        SET strDistrict = LTRIM(strDistrict);
        SET strDistrict = RTRIM(strDistrict);

            IF LENGTH(strDistrict) > 4  OR LENGTH(strDistrict) = 0 THEN RETURN 'ERROR: ' + strDistrict; 
            ELSE 
                BEGIN
                    SET intASCII = ASCII(LEFT(strDistrict, 1));
                    IF  ( intASCII > 47 AND intASCII < 58 ) THEN RETURN 'ERROR: ' + strDistrict;
                    ELSE
                        BEGIN
                            SET intASCII = ASCII(SUBSTRING(strDistrict, 2, 1));
                            IF  ( intASCII > 47 AND intASCII < 58 ) THEN SET strDistrict = LEFT(strDistrict, 1) + ' ' + RIGHT(strDistrict, LENGTH(strDistrict)-1);
                                SET intASCII = ASCII(SUBSTRING(strDistrict, 3, 1));
                            IF  ( intASCII < 48 OR intASCII > 57 ) AND (intASCII <> 32) THEN RETURN 'ERROR: ' + strDistrict; 
                            ELSE IF LENGTH(strDistrict) < 4 THEN     SET strDistrict = LEFT(strDistrict, 2) + ' ' + RIGHT(strDistrict, LENGTH(strDistrict)-2); 
                            END IF;
                        END IF;
                RETURN strDistrict;
            END IF;

`DECL ARE`? Is that a copy/paste error or is that really in your function? — Ocaso Protal, Feb 04 '14 at 12:57
What errors are you getting or which functions you can't convert? — , Feb 04 '14 at 13:00
Your code is incomplete. Can you update code for the second function. — , Feb 04 '14 at 18:34

score 2 · Accepted Answer · 2014-02-04T18:51:17.353

Ok lets start, replace MS-SQL functions with MySQL equivalent functions.

 MSSQL         MySQL
 LEN()         LENGTH()
 SUBTRING()    SUBSTR()
 CHARINDEX()   INSTR()

below is documentation and list of all MySQL String functions http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

Here is correct MySQL Syntax that I have verified.

CREATE FUNCTION FullPostCodeFix (Postcode  VARCHAR(20)) RETURNS VARCHAR(20) 
BEGIN   
    DECLARE district VARCHAR(50);
    DECLARE remainder VARCHAR(50);
    DECLARE sector VARCHAR(50);
        IF LENGTH(Postcode) - INSTR(' ', REVERSE(Postcode)) = LENGTH(Postcode) THEN SET district = SUBSTR(Postcode,1,(LENGTH(Postcode) - 3));
        ELSEIF LENGTH(Postcode) - INSTR(' ', REVERSE(Postcode)) < 3 THEN SET district = SUBSTR(Postcode,1,(LENGTH(Postcode) - 3));
        ELSE SET district = SUBSTR(Postcode, 0, LENGTH(Postcode) - INSTR(' ',     REVERSE(Postcode)) + 1);
        END IF;  
    SET District = dbo.PostcodeFix(District);
    SET Remainder= RIGHT(Postcode,3);
    SET Sector = CONCAT(District,' ',LEFT(Remainder,1));
    SET Postcode = CONCAT(District,' ',Remainder);  
    RETURN Postcode;
END

Here is your second function

CREATE FUNCTION PostCodeFix (strDistrict  VARCHAR(20))  
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN 
    DECLARE intASCII INTEGER;
        SET strDistrict = LTRIM(strDistrict);
        SET strDistrict = RTRIM(strDistrict);

            IF LENGTH(strDistrict) > 4  OR LENGTH(strDistrict) = 0 THEN RETURN 'ERROR: ' + strDistrict; 
            ELSE                 
                    SET intASCII = ASCII(LEFT(strDistrict, 1));
                    IF  ( intASCII > 47 AND intASCII < 58 ) THEN RETURN 'ERROR: ' + strDistrict;
                    ELSE                        
                        SET intASCII = ASCII(SUBSTRING(strDistrict, 2, 1));
                        IF  ( intASCII > 47 AND intASCII < 58 ) THEN SET strDistrict = LEFT(strDistrict, 1) + ' ' + RIGHT(strDistrict, LENGTH(strDistrict)-1);
                        END IF;
                        SET intASCII = ASCII(SUBSTRING(strDistrict, 3, 1));
                        IF  ( intASCII < 48 OR intASCII > 57 ) AND (intASCII <> 32) THEN RETURN 'ERROR: ' + strDistrict; 
                        ELSEIF LENGTH(strDistrict) < 4 THEN     SET strDistrict = LEFT(strDistrict, 2) + ' ' + RIGHT(strDistrict, LENGTH(strDistrict)-2); 
                        END IF;                        
                    END IF;
                RETURN strDistrict;                
            END IF;
END

Thank you, I have replaced the function with the MYSQL equivalent...however I still get the same error. Have I declared the variables correctly? — user1077250, Feb 04 '14 at 13:11
have a look a this screenshot, it may help you understand my error: http://www.greenhill-group.co.uk/images/capture.png — user1077250, Feb 04 '14 at 13:58
It still has the same issue, http://www.greenhill-group.co.uk/images/capture2.png — user1077250, Feb 04 '14 at 14:33
I have updated the answer, this should work. To make sure that it does work you might need to change `DELIMITER` refer to this question http://stackoverflow.com/questions/6628390/mysql-trouble-with-creating-user-defined-function-udf — , Feb 04 '14 at 15:26
:] it has worked although I have noticed the function is calling another function. I have added my attempt at the second function.. I think I am missing something. — user1077250, Feb 04 '14 at 18:31
You forgot several `END IF` statements and used `ELSE IF` instead of `ELSEIF` — , Feb 04 '14 at 18:51

Converting MSSQL function to MySQL function

1 Answers1