1

I've looked through several other possible solutions here on stack overflow but I'm still stuck. The following code is just a simplified version of what I'm trying to do.

Running

select CONVERT( '1510SBrownlee' , SIGNED INTEGER);

works perfeclty fine. It returns 1510 as expected.

The issue arises when the same code is placed in a function. (Again, please note that this is not my actual function, I'm using the following as a simple example... In my actual function, I'm passing a parameter).

After creating

DELIMITER //
CREATE  FUNCTION testing_function() RETURNS varchar(255) 
BEGIN
DECLARE testt varchar(255);

SET testt = CONVERT( '1510SBrownlee' , SIGNED INTEGER);
RETURN testt;
END //
DELIMITER ;

I get the following error when I run select testing_function();

ERROR 1292 (22007): Truncated incorrect INTEGER value: '1510SBrownlee'

Why is this working when I run it directly as opposed to when in a function? Is there a way to make that work in the function? I've also tried CAST as SIGNED as opposed to CONVERT. Same issue.

Thanks in advance.

myohmy
  • 39
  • 4
  • 1
    Does [How to get only Digits from String in mysql?](https://stackoverflow.com/questions/37268248/how-to-get-only-digits-from-string-in-mysql) help? – Andrew Morton Aug 11 '22 at 18:21
  • @AndrewMorton Thanks for sharing this! I used the idea behind that and added a 0 at the end of the string before converting it. That works perfectly. ```CONVERT(concat( '1510SBrownlee', '0') , SIGNED INTEGER);``` – myohmy Aug 11 '22 at 18:29
  • For that method, I'd be concerned with what would happen if the string had no alpha characters after the numeric characters. – Andrew Morton Aug 11 '22 at 18:37
  • @AndrewMorton good point. For the purpose of my project, it will always have an alpha character, but that's good to keep in mind. – myohmy Aug 11 '22 at 18:40

1 Answers1

0

Alright, it works when I convert to DECIMAL. I don't know why but hey, it works. I'll take other answers though!!

DELIMITER //
CREATE  FUNCTION testing_function() RETURNS varchar(255) 
BEGIN
DECLARE testt varchar(255);

SET testt = CONVERT( '1510SBrownlee' , DECIMAL);
RETURN testt;
END //
DELIMITER ;
myohmy
  • 39
  • 4