strpos NOT doing what I'd expect

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!