MYSQL: self written string-manipulation function returns unexpected result

Question

I'm trying to implement a MYSQL function MY_LEFT_STR(STRING x,INT position) in such a way that

MY_LEFT_STR('HELLO', 4) => returns 'HELL' (same as internal LEFT function)
MY_LEFT_STR('HELLO',-1) => returns 'HELL'

DROP FUNCTION IF EXISTS MY_LEFT_STR;
CREATE FUNCTION MY_LEFT_STR(
  in_str VARCHAR(255),
  pos INT
)
RETURNS VARCHAR(255)
BEGIN
  IF (pos < 0) THEN 
      RETURN LEFT(in_str,LENGTH(in_str) - pos);
  ELSE          
    RETURN LEFT(in_str,pos);  
  END IF;           
END;

the result is

select left_str('HELLO', 4)            as A
     , left_str('HELLO',-1)            as B
     , left('HELLO',length('HELLO')-1) as C
from dual

+-----+-----+-----+
| A   | B   | C   |
+-----+-----+-----+
|HELL |HELLO|HELL | 
+-----+-----+-----+

QUESTION What is wrong with my function declaration? (Besides a generall lack of testing for bordercases like MY_LEFT_STR('A',-4) ...

ANSWER: so embarassing ... the answer lies in the double negative for pos=-1 in

RETURN LEFT(in_str,LENGTH(in_str) - pos);

this should be

RETURN LEFT(in_str,LENGTH(in_str) + pos);

Bill Karwin · Accepted Answer · 2009-08-20 07:24:37Z

2

Here's a clue: What's the result of LENGTH(in_str) - (-1)?

When pos is negative, then LENGTH(in_str) - pos yields a number longer than the length of the string. So LEFT() is bound to return the whole string, because you're asking for more characters than the total length of the string.

edited Aug 20, 2009 at 7:24

answered Aug 20, 2009 at 7:10

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

butterchicken · Accepted Answer · 2009-08-20 07:12:00Z

2

RETURN LEFT(in_str,LENGTH(in_str) - pos);

If pos is negative, won't LENGTH(in_str) - pos give you (for your example):

LENGTH(HELLO) - (-1) = 6?

answered Aug 20, 2009 at 7:12

butterchicken

13.9k3 gold badges35 silver badges43 bronze badges

Collectives™ on Stack Overflow

MYSQL: self written string-manipulation function returns unexpected result

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related