This page in other versions: 9.0 / 9.1 / 9.2 / 9.3 / 9.4  |  Development versions: devel  |  Unsupported versions: 7.1 / 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4

4.4. String Functions and Operators

This section describes functions and operators for examining and manipulating string values. Strings in this context include values of all the types CHARACTER, CHARACTER VARYING, and TEXT. Unless otherwise noted, all of the functions listed below work on all of these types, but be wary of potential effects of the automatic padding when using the CHARACTER type. Generally, the functions described here also work on data of non-string types by converting that data to a string representation first. Some functions also exist natively for bit-string types.

SQL defines some string functions with a special syntax where certain keywords rather than commas are used to separate the arguments. Details are in Table 4-6. These functions are also implemented using the regular syntax for function invocation. (See Table 4-7.)

Table 4-6. SQL String Functions and Operators

Function Return Type Description Example Result
string || string text string concatenation 'Postgre' || 'SQL' PostgreSQL
bit_length(string) integer number of bits in string bit_length('jose') 32
char_length(string) or character_length(string) integer number of characters in string char_length('jose') 4
lower(string) text Convert string to lower case. lower('TOM') tom
octet_length(string) integer number of bytes in string octet_length('jose') 4
position(substring in string) integer location of specified substring position('om' in 'Thomas') 3
substring(string [from integer] [for integer]) text extract substring substring('Thomas' from 2 for 3) hom
trim([leading | trailing | both] [characters] from string) text Removes the longest string containing only the characters (a space by default) from the beginning/end/both ends of the string. trim(both 'x' from 'xTomxx') Tom
upper(string) text Convert string to upper case. upper('tom') TOM

Additional string manipulation functions are available and are listed below. Some of them are used internally to implement the SQL-standard string functions listed above.

Table 4-7. Other String Functions

Function Return Type Description Example Result
ascii(text) integer Returns the ASCII code of the first character of the argument. ascii('x') 120
btrim(string text, trim text) text Remove (trim) the longest string consisting only of characters in trim from the start and end of string. btrim('xyxtrimyyx','xy') trim
chr(integer) text Returns the character with the given ASCII code. chr(65) A
convert(string text, [src_encoding name,] dest_encoding name) text Converts string using dest_encoding. The original encoding is specified by src_encoding. If src_encoding is omitted, database encoding is assumed. convert('text_in_unicode', 'UNICODE', 'LATIN1') text_in_unicode represented in ISO 8859-1
initcap(text) text Converts first letter of each word (whitespace separated) to upper case. initcap('hi thomas') Hi Thomas
length(string) integer length of string length('jose') 4
lpad(string text, length integer [, fill text]) text Fills up the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right). lpad('hi', 5, 'xy') xyxhi
ltrim(string text, trim text) text Removes the longest string containing only characters from trim from the start of the string. ltrim('zzzytrim','xyz') trim
pg_client_encoding() name Returns current client encoding name. pg_client_encoding() SQL_ASCII
repeat(text, integer) text Repeat text a number of times. repeat('Pg', 4) PgPgPgPg
rpad(string text, length integer [, fill text]) text Fills up the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated. rpad('hi', 5, 'xy') hixyx
rtrim(string text, trim text) text Removes the longest string containing only characters from trim from the end of the string. rtrim('trimxxxx','x') trim
strpos(string, substring) text Locates specified substring. (same as position(substring in string), but note the reversed argument order) strpos('high','ig') 2
substr(string, from [, count]) text Extracts specified substring. (same as substring(string from from for count)) substr('alphabet', 3, 2) ph
to_ascii(text [, encoding]) text Converts text from multibyte encoding to ASCII. to_ascii('Karel') Karel
translate(string text, from text, to text) text Any character in string that matches a character in the from set is replaced by the corresponding character in the to set. translate('12345', '14', 'ax') a23x5
encode(data bytea, type text) text Encodes binary data to ASCII-only representation. Supported types are: 'base64', 'hex', 'escape'. encode('123\\000\\001', 'base64') MTIzAAE=
decode(string text, type text) bytea Decodes binary data from string previously encoded with encode(). Parameter type is same as in encode(). decode('MTIzAAE=', 'base64') 123\000\001

The to_ascii function supports conversion from LATIN1, LATIN2, WIN1250 (CP1250) only.

Comments


June 19, 2002, 2:32 a.m.

If you want to concatenate a string with a numeric variable field, you cannot simply make a casting of numeric var to string, because it's not allowed. You should first make a casting of numeric var to integer, and then postgresql itself makes the casting of integer to string. Here's an example:

if string_field = 'AAA' and num_var = 23 then

table.string_field || CAST(table.num_var AS INTEGER)

returns 'AAA23'


Aug. 3, 2002, 9:31 p.m.

@Jackson:
NULL is not ''. As a general rule, any operator will return NULL if one of it's arguments is NULL. You can use COALESCE(var,'') to force the behaviour you want.


Nov. 6, 2002, 8:03 p.m.

Here's a useful function to replace within a string:

/*
replace all occurences of 'match' in 'subject' with 'replace'. Result is returned
*/

CREATE OR REPLACE FUNCTION replace(VARCHAR, VARCHAR, VARCHAR) RETURNS VARCHAR AS '
DECLARE
subject ALIAS for $1;
match ALIAS for $2;
replace ALIAS for $3;
r varchar;
matchpos int;
remain varchar;
rempos int;
BEGIN

if (char_length(match) = 0) then
raise exception ''replace function was called with null match string. This is not permitted.'';
end if;

remain := subject;
r := '''';
matchpos := strpos(subject,match);
WHILE (matchpos > 0 ) LOOP
r := r || substring(remain, 0,matchpos) || replace;
rempos := matchpos + char_length(match);
remain := substring(remain,rempos);
matchpos := strpos(remain,match);
END LOOP;

r := r || remain;
return r;

END;

' LANGUAGE 'plpgsql';


May 29, 2003, 9:44 p.m.

There is soundex support actually. You just have to turn it on.
It\'s as simple as:

\\i /usr/share/pgsql/contrib/fuzzystrmatch.sql

on my RH7.3 distribution. YMMV.

-Dave


Sept. 20, 2003, 5:12 p.m.

To use string concatination in select statement as given bellow......

select c.status||\' this \'||\' is \'||\' the \'||\' status \'||course_id||\' is the \'||\' Course\'||\' id \' from course_master as c;

Regards
Yogesh

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group