Needed help to converting mssql functions into mysql

Question

Could anyone help me to solve issues with converting MsSQL to MySQL? It gives a syntax error.

Original Mssql function:

CREATE  FUNCTION StrToNum (srt1 varchar(250))  
RETURNS real AS  
 BEGIN 
DECLARE t real
IF srt1 IS NOT NULL and ISNUMERIC(srt1)=1 and PATINDEX('%,%',srt1)=0 and 
PATINDEX('%e%',srt1)=0
SET t=CONVERT(Money,srt1)
ELSE
SET t=NULL
RETURN t
END

i tried like this as mysql

DELIMITER $$  
CREATE  FUNCTION StrToNum (srt1 VARCHAR(250))   
RETURNS REAL DETERMINISTIC  
BEGIN   
DECLARE t REAL;  
IF srt1 IS NOT NULL AND srt1 > 0 AND POSITION('%,%' IN srt1=0) AND POSITION('%e%' IN srt1=0)  
THEN SET t=CONVERT(Money,INT);  
ELSE  
THEN SET t=NULL; END IF;  
RETURN t;  
END IF;  
END $$  
DELIMITER;

What is DELMIMITER$$? Add ';'. Then try to change Sql Server functions with alternative MySQL functions. — Devart
– Devart, Commented Aug 3, 2012 at 12:40
It's not DELMIMITER, it's DELIMITER - the extra M you have in the fourth position is probably a big part of your syntax problem. There is no M following the L; remove it. — Ken White
– Ken White, Commented Aug 3, 2012 at 12:44
Mark Byers i;ve updated my question. Devart Delimiter is not Problem. i created couple of function using this. — Raheel
– Raheel, Commented Aug 3, 2012 at 12:44

Jeremy Smyth · Accepted Answer · 2012-08-03 13:03:27Z

This code should parse:

DELIMITER $$  
CREATE  FUNCTION StrToNum (srt1 VARCHAR(250))   
RETURNS REAL DETERMINISTIC  
BEGIN   
DECLARE t REAL;  
IF srt1 IS NOT NULL AND srt1 > 0 
    AND POSITION('%,%' IN srt1=0) 
    AND POSITION('%e%' IN     srt1=0)  
THEN SET t=CONVERT(srt1,signed);  --(1)
ELSE  
 SET t=NULL; END IF;              --(2)
RETURN t;   
END $$                            --(3)
DELIMITER ;                       --(4)

I'll break it down for you:

CONVERT() takes SIGNED or UNSIGNED as target types. INT doesn't work. (1)
You brought forward a error from your CONVERT() syntax in mssql; you used Money instead of srt1 (1). The reason for the confusion is that CONVERT() in MSSQL and MySQL have their parameters reversed.
ELSE doesn't need (or accept) a THEN keyword after it (2)
You had an extra END IF before the final END$$ (3)
DELIMITER ; requires a space (4)

Collectives™ on Stack Overflow

Needed help to converting mssql functions into mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related