(crossposted to -hackers, should follow up on that list) > Well, OVERLAY is defined as: > overlay(string placing string from integer [for integer]) > and replace() is defined (by me at least) as: > replace(inputstring, old-substr, new-substr)
OK. > OVERLAY requires that I know the "from" position and possibly the "for" > in advance. Other functions (such as strpos() and substr()) can be used > to help... Right. So you can do your example pretty easily: thomas=# select overlay(f1 placing '' from position('/local' in f1) thomas-# for length('/local')) from strtest; overlay -------------------- /usr/pgsql/data /m1/usr/pgsql/data And if you don't like that much typing you can do: thomas=# create function replace(text, text, text) returns text as ' thomas'# select overlay($1 placing $3 from position($2 in $1) for length($2)); thomas'# ' language 'sql'; CREATE FUNCTION thomas=# select replace(f1, '/local', '') from strtest; replace -------------------- /usr/pgsql/data /m1/usr/pgsql/data > But now what happens if you wanted to replace all of the '/' characters > with '\'?... > You can't do this at all with overlay(), unless you want to write a > PL/pgSQL function and loop through each string. I started out with > exactly this, using strpos() and substr(), but I thought a C function > was cleaner, and it is certainly faster. OK, this is in the "can't do it what we have" category. Should we have it accept a regular expression rather than a simple string? In either case it should probably go into the main distro. Except that I see "REPLACE" is mentioned as a reserved word in SQL99. But has no other mention in my copy of the draft standard. Anyone else have an idea what it might be used for in the standard? The other functions look useful too, unless to_char() and varbit can be evolved to support this functionality. - Thomas ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])