CODEWITHSUNDEEP
mysqlsql-server
Raheel
Raheel

Reputation: 177

Needed help to converting mssql functions into mysql

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;

Upvotes: 1

Views: 443

Answers (1)

Jeremy Smyth
Jeremy Smyth

Reputation: 23493

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)

Upvotes: 2

Related Questions