pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUppercase and Lowercase
7+ messages / 4 participants
[nested] [flat]
* Uppercase and Lowercase
@ 2007-02-19 15:22 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-02-19 15:25 ` Re: Uppercase and Lowercase Bart Degryse <Bart.Degryse@indicator.be>
2007-02-19 15:30 ` Re: Uppercase and Lowercase Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 2 replies; 7+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-02-19 15:22 UTC (permalink / raw)
To: pgsql-sql
Hello
In my PostgreSQL database i have records inserted in Uppercase.
For example: VIA SENATO
What i want is to change them to "Via Senato". Ofcourse i'm looking for a automatico way. I wrote this code update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) But it doesn't work.
When i run only update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) It gets me the first letter and when i run
update registro1 set pa_indirizzo = lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) I get from the second letter to the last. But when i join them them don't work..
Anyone got any idea??
Thanks
Shavonne Wijesinghe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Uppercase and Lowercase
2007-02-19 15:22 Uppercase and Lowercase Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2007-02-19 15:25 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-02-19 15:39 ` Re: Uppercase and Lowercase Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
1 sibling, 1 reply; 7+ messages in thread
From: Bart Degryse @ 2007-02-19 15:25 UTC (permalink / raw)
To: pgsql-sql
Replace && by ||
>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2007-02-19 16:22 >>>
Hello
In my PostgreSQL database i have records inserted in Uppercase.
For example: VIA SENATO
What i want is to change them to "Via Senato". Ofcourse i'm looking for a automatico way. I wrote this code update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) But it doesn't work.
When i run only update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) It gets me the first letter and when i run
update registro1 set pa_indirizzo = lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) I get from the second letter to the last. But when i join them them don't work..
Anyone got any idea??
Thanks
Shavonne Wijesinghe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Uppercase and Lowercase
2007-02-19 15:22 Uppercase and Lowercase Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-02-19 15:25 ` Re: Uppercase and Lowercase Bart Degryse <Bart.Degryse@indicator.be>
@ 2007-02-19 15:39 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-02-19 15:48 ` Re: Uppercase and Lowercase Bart Degryse <Bart.Degryse@indicator.be>
2007-02-19 15:54 ` Re: Uppercase and Lowercase Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 7+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-02-19 15:39 UTC (permalink / raw)
To: Bart Degryse <Bart.Degryse@indicator.be>; pgsql-sql
Hey thanks Bart. it worked ;)
but sadly what it does is changes "VIA SENATO" in to "Via senato" but what i need is "Via Senato"
Anyoneeeeeee??
Shavonne Wijesinghe
----- Original Message -----
From: Bart Degryse
To: pgsql-sql@postgresql.org
Sent: Monday, February 19, 2007 4:25 PM
Subject: Re: [SQL] Uppercase and Lowercase
Replace && by ||
>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2007-02-19 16:22 >>>
Hello
In my PostgreSQL database i have records inserted in Uppercase.
For example: VIA SENATO
What i want is to change them to "Via Senato". Ofcourse i'm looking for a automatico way. I wrote this code update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) But it doesn't work.
When i run only update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) It gets me the first letter and when i run
update registro1 set pa_indirizzo = lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) I get from the second letter to the last. But when i join them them don't work..
Anyone got any idea??
Thanks
Shavonne Wijesinghe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Uppercase and Lowercase
2007-02-19 15:22 Uppercase and Lowercase Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-02-19 15:25 ` Re: Uppercase and Lowercase Bart Degryse <Bart.Degryse@indicator.be>
2007-02-19 15:39 ` Re: Uppercase and Lowercase Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2007-02-19 15:48 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-02-19 16:05 ` Re: Uppercase and Lowercase Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 1 reply; 7+ messages in thread
From: Bart Degryse @ 2007-02-19 15:48 UTC (permalink / raw)
To: pgsql-sql
Of course it does. That's what you ask your query to do. You only used the wrong operator for string concatenation, which is why it didn't seem to work. But your query will not do this for each word in a field. For that you need a function or regular expressions. I suggest you try
select initcap(pa_indirizzo) from ..
Please always reply to pgsql-sql@postgresql.org and not to the senders email address.
>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2007-02-19 16:39 >>>
Hey thanks Bart. it worked ;)
but sadly what it does is changes "VIA SENATO" in to "Via senato" but what i need is "Via Senato"
Anyoneeeeeee??
Shavonne Wijesinghe
----- Original Message -----
From: Bart Degryse ( mailto:Bart.Degryse@indicator.be )
To: pgsql-sql@postgresql.org
Sent: Monday, February 19, 2007 4:25 PM
Subject: Re: [SQL] Uppercase and Lowercase
Replace && by ||
>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2007-02-19 16:22 >>>
Hello
In my PostgreSQL database i have records inserted in Uppercase.
For example: VIA SENATO
What i want is to change them to "Via Senato". Ofcourse i'm looking for a automatico way. I wrote this code update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) But it doesn't work.
When i run only update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) It gets me the first letter and when i run
update registro1 set pa_indirizzo = lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) I get from the second letter to the last. But when i join them them don't work..
Anyone got any idea??
Thanks
Shavonne Wijesinghe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Uppercase and Lowercase
2007-02-19 15:22 Uppercase and Lowercase Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-02-19 15:25 ` Re: Uppercase and Lowercase Bart Degryse <Bart.Degryse@indicator.be>
2007-02-19 15:39 ` Re: Uppercase and Lowercase Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-02-19 15:48 ` Re: Uppercase and Lowercase Bart Degryse <Bart.Degryse@indicator.be>
@ 2007-02-19 16:05 ` Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 0 replies; 7+ messages in thread
From: Bart Degryse @ 2007-02-19 16:05 UTC (permalink / raw)
To: pgsql-sql
Robert is right of course. You can eg use a trigger to do that...
I haven't tested, but I guess something like this would do what you want whenever you insert records in your table
CREATE OR REPLACE FUNCTION "public"."func_change_case" () RETURNS trigger AS
$body$
BEGIN
NEW.pa_indirizzo := initcap(NEW.pa_indirizzo);
RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TRIGGER "TRIG_yourtablename" BEFORE INSERT
ON "public"."yourtablename" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_change_case"();
>>> "Hiltibidal, Robert" <Robert.Hiltibidal@argushealth.com> 2007-02-19 16:55 >>>
Exactly. Since its only a select group of words this query applies to its better to correct for this before the information goes into the database.
From:pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Bart Degryse
Sent: Monday, February 19, 2007 9:49 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Uppercase and Lowercase
Of course it does. That's what you ask your query to do. You only used the wrong operator for string concatenation, which is why it didn't seem to work. But your query will not do this for each word in a field. For that you need a function or regular expressions. I suggest you try
select initcap(pa_indirizzo) from ..
Please always reply to pgsql-sql@postgresql.org and not to the senders email address.
>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2007-02-19 16:39 >>>
Hey thanks Bart. it worked ;)
but sadly what it does is changes "VIA SENATO" in to "Via senato" but what i need is "Via Senato"
Anyoneeeeeee??
Shavonne Wijesinghe
----- Original Message -----
From:Bart Degryse ( mailto:Bart.Degryse@indicator.be )
To:pgsql-sql@postgresql.org
Sent:Monday, February 19, 2007 4:25 PM
Subject:Re: [SQL] Uppercase and Lowercase
Replace && by ||
>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2007-02-19 16:22 >>>
Hello
In my PostgreSQL database i have records inserted in Uppercase.
For example: VIA SENATO
What i want is to change them to "Via Senato". Ofcourse i'm looking for a automatico way. I wrote this code update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) But it doesn't work.
When i run only update registro1 set pa_indirizzo = upper(substr(pa_indirizzo, 1, 1)) It gets me the first letter and when i run
update registro1 set pa_indirizzo = lower(substr(pa_indirizzo, 2, length(pa_indirizzo) - 1)) I get from the second letter to the last. But when i join them them don't work..
Anyone got any idea??
Thanks
Shavonne Wijesinghe
PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmaster@argushealth.com. Thank you.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Uppercase and Lowercase
2007-02-19 15:22 Uppercase and Lowercase Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-02-19 15:25 ` Re: Uppercase and Lowercase Bart Degryse <Bart.Degryse@indicator.be>
2007-02-19 15:39 ` Re: Uppercase and Lowercase Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2007-02-19 15:54 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2007-02-19 15:54 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: Bart Degryse <Bart.Degryse@indicator.be>; pgsql-sql
Shavonne Marietta Wijesinghe wrote:
> Hey thanks Bart. it worked ;)
>
> but sadly what it does is changes "VIA SENATO" in to "Via senato" but what i need is "Via Senato"
>
> Anyoneeeeeee??
initcap() does what you want.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Uppercase and Lowercase
2007-02-19 15:22 Uppercase and Lowercase Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2007-02-19 15:30 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 7+ messages in thread
From: Andrew Sullivan @ 2007-02-19 15:30 UTC (permalink / raw)
To: pgsql-sql
On Mon, Feb 19, 2007 at 04:22:06PM +0100, Shavonne Marietta Wijesinghe wrote:
> Hello
>
> In my PostgreSQL database i have records inserted in Uppercase.
> For example: VIA SENATO
>
> What i want is to change them to "Via Senato".
Have a look at the initcap() function.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
If they don't do anything, we don't need their acronym.
--Josh Hamilton, on the US FEMA
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-02-19 16:05 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-19 15:22 Uppercase and Lowercase Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-02-19 15:25 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-02-19 15:39 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-02-19 15:48 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-02-19 16:05 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-02-19 15:54 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-02-19 15:30 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox