How to create StartsWith function for char(n) type with ending space
От | Andrus |
---|---|
Тема | How to create StartsWith function for char(n) type with ending space |
Дата | |
Msg-id | D160A7990C734025BFA0685E582771F6@dell2 обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: