Re: strpos NOT doing what I'd expect - Mailing list 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!