MYSQL:自己编写的字符串操作函数返回意外结果

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

我正在尝试实现一个MYSQL函数MY_LEFT_STR(STRING x,INT position)

MY_LEFT_STR('HELLO', 4) => returns 'HELL' (same as internal LEFT function)
MY_LEFT_STR('HELLO'， 4) =>返回'HELL'(与内部左函数相同)
MY_LEFT_STR('HELLO',-1) => returns 'HELL'
MY_LEFT_STR('你好',1)= >返回“地狱”

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) ...

问题是我的函数声明有什么问题?(除了对MY_LEFT_STR(' a '，-4)等边界条件的测试外，还缺乏测试)。

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

答:所以尴尬…答案是o =-1的双重否定

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

this should be

这应该是

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

2 个解决方案

#1

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

这里有一个提示:长度(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.

当pos为负时，那么LENGTH(in_str)—pos产生的数字要比字符串的长度长。左()一定会返回整个字符串，因为你要求的字符比字符串的总长度要多。

#2

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

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

如果pos是负数，就不会有长度(in_str)——pos会给你(例如):

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

#1