Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 3AF9F9FB7CA for ; Mon, 19 Feb 2007 12:05:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 55811-07 for ; Mon, 19 Feb 2007 12:05:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id A07C39FB7B3 for ; Mon, 19 Feb 2007 12:05:18 -0400 (AST) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Mon, 19 Feb 2007 16:53:07 +0100 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Mon, 19 Feb 2007 17:05:16 +0100 Message-Id: <45D9D8BE.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Mon, 19 Feb 2007 17:05:03 +0100 From: "Bart Degryse" To: Subject: Re: Uppercase and Lowercase References: <45D9D4E9.A3DD.0030.0@indicator.be> In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part81A510BF.0__=" X-imss-version: 2.046 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.112 tagged_above=0 required=5 tests=AWL, BAYES_40, FORGED_RCVD_HELO, HTML_MESSAGE, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200702/199 X-Sequence-Number: 27533 --=__Part81A510BF.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Robert is right of course. You can eg use a trigger to do that... =20 I haven't tested, but I guess something like this would do what you want = whenever you insert records in your table =20 CREATE OR REPLACE FUNCTION "public"."func_change_case" () RETURNS trigger = AS $body$ BEGIN NEW.pa_indirizzo :=3D initcap(NEW.pa_indirizzo); RETURN NEW; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; =20 CREATE TRIGGER "TRIG_yourtablename" BEFORE INSERT=20 ON "public"."yourtablename" FOR EACH ROW=20 EXECUTE PROCEDURE "public"."func_change_case"(); =20 >>> "Hiltibidal, Robert" 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. =20 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=20 Subject: Re: [SQL] Uppercase and Lowercase =20 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" = 2007-02-19 16:39 >>> Hey thanks Bart. it worked ;)=20 =20 but sadly what it does is changes "VIA SENATO" in to "Via senato" but = what i need is "Via Senato" =20 Anyoneeeeeee?? =20 =20 =20 Shavonne Wijesinghe ----- Original Message -----=20 From:Bart Degryse ( mailto:Bart.Degryse@indicator.be )=20 To:pgsql-sql@postgresql.org=20 Sent:Monday, February 19, 2007 4:25 PM Subject:Re: [SQL] Uppercase and Lowercase =20 Replace && by || >>> "Shavonne Marietta Wijesinghe" = 2007-02-19 16:22 >>> Hello =20 In my PostgreSQL database i have records inserted in Uppercase.=20 For example: VIA SENATO =20 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 =3D = upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizzo, 2, = length(pa_indirizzo) - 1)) But it doesn't work. =20 When i run only update registro1 set pa_indirizzo =3D upper(substr(pa_indir= izzo, 1, 1)) It gets me the first letter and when i run update registro1 set pa_indirizzo =3D 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.. =20 =20 Anyone got any idea?? =20 Thanks =20 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 transmissi= on in error, please delete the email and immediately notify the sender via = the email return address or mailto:postmaster@argushealth.com. Thank you. --=__Part81A510BF.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
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 := =3D 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"."you= rtablename" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_change_case"(= );
 


>>> "Hiltibidal, Robert" <Robert.Hiltibidal@argush= ealth.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
<= SPAN style=3D"FONT-WEIGHT: bold">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@postg= resql.org and not to the senders email address.<= /FONT>


>>&= gt; "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

Sent: Monday, February 19, 2007 4:25 = PM

Subject: Re: [SQL] Uppercase and = Lowercase

 

Replace = && by ||

>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studiofor= m.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 =3D 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 = =3D upper(substr(pa_indirizzo, 1, 1)) It gets me = the first letter and when i run<= SPAN style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">

update registro1 set pa_indirizzo =3D 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<= /FONT>

 

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 transmissi=
on in error, please delete the email and immediately notify the sender via =
the email return address or mailto:postmaster@argushealth.com.  Thank you.

--=__Part81A510BF.0__=--