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

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: <pgsql-general(at)postgresql(dot)org>
Subject: How to create StartsWith function for char(n) type with ending space
Date: 2018-07-06 08:54:36
Message-ID: D160A7990C734025BFA0685E582771F6@dell2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

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.

Using sample data below

startswith( test, 'A')
startswith( test, 'A ')
StartsWith(test, rpad('A',19) )

shuld return true

but

startswith( test, RPAD( 'A', 20)) 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' );

select StartWith(test, 'A ' ) from test

posted also in

https://stackoverflow.com/questions/51206529/how-to-create-startswith-function-for-charn-type-with-ending-space

Browse pgsql-general by date

  From Date Subject
Next Message Boblitz John 2018-07-06 08:59:46 RE: Unable to Connect to DB Instance (SOLVED)
Previous Message Pavel Stehule 2018-07-06 08:26:41 Re: How to remove elements from array .