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
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
The to_ascii function supports
conversion from LATIN1, LATIN2, WIN1250 (CP1250) only.
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'
@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.
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';
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
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