Mysql 子字符串使用 instr()

Question

我想从字符串中提取邮政编码。所以很自然地，我想到了这样的事情:

substring(PERSON_ADDRESS, instr(PERSON_ADDRESS,'[0-9][0-9][0-9][0-9][0-9]'), 5)


唉，看来 instr(str, '[0-9]) 在 mysql 中不是有效的(它返回 0)

知道如何做到这一点吗？

非常感谢!

Answer 1

您创建一个用户定义的函数 - 只要潜在的邮政编码被空格括起来或位于最后一个标记位置，这应该起作用

drop function if exists tokens;
delimiter //
create     function tokens(instring varchar(255))
returns varchar(255)
begin
declare   tempstring varchar(100);
declare   outstring  varchar(100);
declare  checkit int;
set tempstring = ltrim(rtrim(instring));
set checkit = 0;
looper: while   tempstring is not null and instr(tempstring,' ') > 0 do
        set outstring = substr(tempstring,1,instr(tempstring, ' ') - 1);
        set tempstring = ltrim(rtrim(replace(tempstring,outstring,'')));
        if length(outstring) = 5 then 
            if substring(outstring,1,1) between char(48) and char(57) and
                substring(outstring,2,1) between char(48) and char(57) and
                substring(outstring,3,1) between char(48) and char(57) and
                substring(outstring,4,1) between char(48) and char(57) and
                substring(outstring,5,1) between char(48) and char(57) then
                set checkit = 1;
                leave looper;
            end if;
        end if;
end while; 
if checkit = 0 then 
    set outstring = tempstring; 
            if length(outstring) = 5 then 
            if substring(outstring,1,1) between char(48) and char(57) and
                substring(outstring,2,1) between char(48) and char(57) and
                substring(outstring,3,1) between char(48) and char(57) and
                substring(outstring,4,1) between char(48) and char(57) and
                substring(outstring,5,1) between char(48) and char(57) then
                set checkit = 1;
            end if;
        end if;
end if;

if checkit = 0 then set outstring = 'NotFound'; end if;
return outstring;
end //
delimiter ;

鉴于此

+------+---------------------+
| id   | address             |
+------+---------------------+
|    1 | 13 mont 12c45 st    |
|    2 | 13 mont 12345 st    |
|    3 | 13 mont 12c45 45678 |
|    4 | 56789 mont 12c45 st |
+------+---------------------+

函数返回此

+------+---------------------+----------+
| id   | address             | zipcode  |
+------+---------------------+----------+
|    1 | 13 mont 12c45 st    | NotFound |
|    2 | 13 mont 12345 st    | 12345    |
|    3 | 13 mont 12c45 45678 | 45678    |
|    4 | 56789 mont 12c45 st | 56789    |
+------+---------------------+----------+