Thread: strpos NOT doing what I'd expect
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 := '' ;
j int := 0 ;
BEGIN
IF length(delimlist) = 0 THEN
RAISE NOTICE 'In function \'find_next_delim\' the delimiter cannot be null.' ;
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!
Ralph Smith <smithrn@washington.edu> writes: > DECLARE > achar character := '' ; Use varchar or text. character is weird about trailing spaces. regards, tom lane
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!