Re: strpos NOT doing what I'd expect
От | Alban Hertroys |
---|---|
Тема | Re: strpos NOT doing what I'd expect |
Дата | |
Msg-id | C3707B8C-19E1-4158-9902-8058AEE187EE@solfertje.student.utwente.nl обсуждение исходный текст |
Ответ на | strpos NOT doing what I'd expect (Ralph Smith <smithrn@washington.edu>) |
Список | pgsql-general |
On Jun 7, 2008, at 2:58 AM, Ralph Smith wrote: > CODE: > =============================== > CREATE OR REPLACE FUNCTION find_next_delim(invar varchar, delimlist > varchar) RETURNS integer AS > $$ > > /* OVERLOADED Function. The other version takes a 3rd parameter > as the > starting position in invar. > */ > > DECLARE > > achar character := '' ; It's because you're using character here instead of text. Character collapses whitespace (it's usually used as char(<some length>). To demonstrate: development=> select ''''||CAST (' '::character AS text)||''''; ?column? ---------- '' So your comparison becomes: development=> SELECT strpos('3', ''); strpos -------- 1 Now that's got to be a corner case of the use of strpos, I'm not entirely sure that'd be the right behaviour, but if it isn't, what would be? Does a non-empty string contain empty strings? And if so, is it at position 1? The character at position 1 is actually '3' after all... Maybe it should return NULL (unknown) or raise an error? When using text instead of character, your function works as expected. > j int := 0 ; > > BEGIN > > IF length(delimlist) = 0 THEN > RAISE NOTICE 'In function \'find_next_delim\' the delimiter > cannot be null.' ; An empty string is not null. If someone would actually enter NULL for delimlist your function would break: development=> select find_next_delim(NULL,'3') ; ERROR: upper bound of FOR loop cannot be NULL CONTEXT: PL/pgSQL function "find_next_delim" line 18 at FOR with integer loop variable > END IF ; > > > FOR i IN 1 .. length(invar) > LOOP > > j := j + 1 ; > achar := substring(invar from i for 1 ) ; > RAISE NOTICE 'achar is R%S',achar ; > IF strpos(delimlist,achar) <> 0 THEN > RETURN j ; > END IF ; > > END LOOP ; > > RETURN 0 ; > > END ; > $$ LANGUAGE plpgsql ; /* find_next_delim */ > > > > > WHAT'S HAPPENING: > =============================== > airburst=# select find_next_delim('ralph smith','3') ; > > NOTICE: achar is RrS > NOTICE: achar is RaS > NOTICE: achar is RlS > NOTICE: achar is RpS > NOTICE: achar is RhS > NOTICE: achar is R S > find_next_delim > ----------------- > 6 > (1 row) > > > airburst=# select find_next_delim('ralph smith','') ; -- for the > heck of it, that's a null > > NOTICE: In function 'find_next_delim' the delimiter cannot be null. > NOTICE: achar is RrS > NOTICE: achar is RaS > NOTICE: achar is RlS > NOTICE: achar is RpS > NOTICE: achar is RhS > NOTICE: achar is R S > find_next_delim > ----------------- > 6 > (1 row) > > WHY find a match on the space??? > > Thanks! > > Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,484a5f1e927662100280104!
В списке pgsql-general по дате отправления: