Mysql substring using instr()

Question

I want to extract a zip code from a string. So naturally, I thought of something like this:

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

Alas, it seems that instr(str, '[0-9]) is not something valid in mysql (it returns 0)

Any idea for how to do this ?

Many thanks!

I don't think INSTR supports regex. Can you show use a sample which you are trying to extract? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 20, 2016 at 11:37
If you show us an example of PERSON_ADDRESS someone will show you how to do the extraction — RiggsFolly
– RiggsFolly, Commented Jul 20, 2016 at 11:41
Although my first thought would be that you have made a basic error in your database design, Why is the zipcode not in its own column — RiggsFolly
– RiggsFolly, Commented Jul 20, 2016 at 11:43
The problem is that the PERSON_ADDRESS field is not strictly homogenous. The only thing for sure is that there is only one succession of 5 digits, which is what I would like to extract As for poor database design, these data are not mine in the first place unfortunately :( — user3677552
– user3677552, Commented Jul 20, 2016 at 11:57

P.Salmon · Accepted Answer · 2016-07-20 14:13:18Z

You create a user defined function - as long as the potential zip code is enclosed by space or is in the last token position this should work

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 ;

and given this

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

Function returns this

+------+---------------------+----------+
| 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    |
+------+---------------------+----------+

Collectives™ on Stack Overflow

Mysql substring using instr()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related