By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,373 Members | 1,984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,373 IT Pros & Developers. It's quick & easy.

update field using substrings of another field

P: n/a

Have a table with fnumber and number, both text
fnumber is a phone number, format "8005551212"
number needs to be the same number but in the format "(800) 555-1212"

we currently run the following each time the list is updated;
update pnums set number='(' || substring(fnumber from 1 for 3) || ') ' ||
substring(fnumber from 4 for 3) || '-' || substring(fnumber from 7 for 4);

is there a way to statically set that number field so that it always equals the
fnumber in that format, or automatically updates itself when the fnumber field
is changed?

thanks

Dave

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Dave [Hawk-Systems]" <da**@hawk-systems.com> writes:
Have a table with fnumber and number, both text
fnumber is a phone number, format "8005551212"
number needs to be the same number but in the format "(800) 555-1212"

we currently run the following each time the list is updated;
update pnums set number='(' || substring(fnumber from 1 for 3) || ') ' ||
substring(fnumber from 4 for 3) || '-' || substring(fnumber from 7 for 4);

is there a way to statically set that number field so that it always
equals the fnumber in that format, or automatically updates itself
when the fnumber field is changed?


You could do it fairly easily with a trigger.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #2

P: n/a
Doug McNaught <do**@mcnaught.org> writes:
"Dave [Hawk-Systems]" <da**@hawk-systems.com> writes:
Have a table with fnumber and number, both text
fnumber is a phone number, format "8005551212"
number needs to be the same number but in the format "(800) 555-1212"
You could do it fairly easily with a trigger.


Or consider plan B: why bother to actually store "number" at all,
if it is trivially computable from "fnumber"?

You could make a view that includes "number" as a derived column,
if you have apps that insist on seeing it as an ordinary-looking
column in the select result.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #3

P: n/a
Wouldn't it be the correct way to do such things just on retrieve of the
data, i.e. to implement the formatted output on the appication layer, or
maybe via a View, so that you don't save the data twice?

Doug McNaught wrote:
"Dave [Hawk-Systems]" <da**@hawk-systems.com> writes:
Have a table with fnumber and number, both text
fnumber is a phone number, format "8005551212"
number needs to be the same number but in the format "(800) 555-1212"

we currently run the following each time the list is updated;
update pnums set number='(' || substring(fnumber from 1 for 3) || ') ' ||
substring(fnumber from 4 for 3) || '-' || substring(fnumber from 7 for 4);

is there a way to statically set that number field so that it always
equals the fnumber in that format, or automatically updates itself
when the fnumber field is changed?


You could do it fairly easily with a trigger.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org


--
Andreas Fromm

-----------------------------
Drink wet cement...
... and get stoned


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.