How to create StartsWith function for char(n) type with ending space

Question

I need to create startswith function which returns true if char(n) database column starts with some characters which may can contain space at end. Spaces should treated like other characters.

Database has two values ´A´ and ´AA´ . I want that startwith('A') (without trailing space) matches with both AA and A but startwith('A ') (with trailing space) matches only with A.

Using sample data below

startswith( test, 'A')   -- works
startswith( test, 'A  ')  -- returns wrong result : false
StartsWith(test, rpad('A',20) )  -- returns wrong result : false

should return true

but

startswith( test, RPAD( 'A', 21))

should return false since there is extra space in end of check string.

Database contains test column which has char(20) type column and this cannot changed.

I tried code below but it returns false.

How to fix this so that it returns true? Using Postgres starting from 9.1

Andrus.

CREATE or replace FUNCTION public.likeescape( str text )
--  
https://stackoverflow.com/questions/10153440/how-to-escape-string-while-matching-pattern-in-postgresql
RETURNS text AS $$
SELECT replace(replace(replace($1,'^','^^'),'%','^%'),'_','^_') ;
$$ LANGUAGE sql IMMUTABLE;

CREATE or replace FUNCTION public.StartWith( cstr text, algusosa text )
RETURNS bool AS $$
SELECT $2 is null or $1 like likeescape($2) ||'%' ESCAPE '^' ;
$$ LANGUAGE sql IMMUTABLE;

create temp table test ( test char(20) ) on commit drop;
insert into test values ('A' );
insert into test values ('AA' );

select StartWith(test, 'A ' ) from test

I also posted this to pgsql-general mailing list.

I don't see what's wrong with the first 3 examples. Given `test` = 'A', `test` starts with 'A', doesn't start with 'A ', and doesn't start with 'A ...'. Why is that not correct? — 404, Jul 06 '18 at 10:01
char(20) is padded with trasiling spaces. So `A ` must match. Database has two values ´A´ and ´AA´ . I want that startwith('A') returns both AA and A but startwith('A ') returns only A. I updated question — Andrus, Jul 06 '18 at 10:39
That's incorrect. `CREATE TEMP TABLE t (txt CHAR(20)); INSERT INTO t VALUES ('A'); SELECT '_' || txt || '_' FROM t;` returns `_A_`, and `SELECT LENGTH(txt) FROM t;` returns `1` — 404, Jul 06 '18 at 10:52

score 2 · Accepted Answer · edited Jul 07 '18 at 06:49

From 8.3. Character Types:

Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type character, and they will be removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values, and when using pattern matching, e.g. LIKE, regular expressions.

Note, both your function's arguments are text. When you pass it the column test and the literal 'A ', test loses the trailing spaces upon the implicit cast whereas the literal doesn't. In your function you'll end up with something like

'A' LIKE 'A %' ESCAPE '^'

which isn't true.

You can overload your function and create a copy of it, where the first argument is char or simply use regular expressions instead of defining your own function, like test ~ '^A ' or rtrim() the spaces off the char and treat it like being "spaceless" like rtrim(test) LIKE 'A%'. I'd prefer one of the latter.

Is it possible to create `StartsWith` function which does not remove spaces when char(n) is passed to it as first argument. n may be different for different queries. I tried `StartsWith ( cstr char(n), StartOfCstrMayContainSpacesAtEnd text )` but this is syntactically incorrect. — Andrus, Jul 07 '18 at 11:09

How to create StartsWith function for char(n) type with ending space

1 Answers1