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 se= nato" 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_indirizz= o =3D upper(substr(pa_indirizzo, 1, 1)) && lower(substr(pa_indirizz= o, 2, length(pa_indirizzo) - 1)) But it = doesn't work.

When i run only update registro1 set pa_indirizzo =3D upper(substr(pa_indi= rizzo, 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..

Anyone got any idea??

Thanks

Shavonne Wijesinghe
--=__PartFBDF6AC9.0__=-- From alvherre@commandprompt.com Mon Feb 19 15:54:18 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C8D1C9FB7F7 for ; Mon, 19 Feb 2007 11:54:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 14705-07 for ; Mon, 19 Feb 2007 11:54:06 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 33CA79FB9C4 for ; Mon, 19 Feb 2007 11:54:11 -0400 (AST) Received: from perhan.alvh.no-ip.org (216-155-79-222.bk1-dsl.surnet.cl [216.155.79.222]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l1JFs9td002220; Mon, 19 Feb 2007 07:54:09 -0800 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 05A8232A00; Mon, 19 Feb 2007 12:54:07 -0300 (CLST) Date: Mon, 19 Feb 2007 12:54:06 -0300 From: Alvaro Herrera To: Shavonne Marietta Wijesinghe Cc: Bart Degryse , pgsql-sql@postgresql.org Subject: Re: Uppercase and Lowercase Message-ID: <20070219155406.GO28395@alvh.no-ip.org> Mail-Followup-To: Shavonne Marietta Wijesinghe , Bart Degryse , pgsql-sql@postgresql.org References: <001001c75439$b7749b50$1102a8c0@dream> <45D9CF8C.A3DD.0030.0@indicator.be> <002701c7543c$2a888730$1102a8c0@dream> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <002701c7543c$2a888730$1102a8c0@dream> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Mon, 19 Feb 2007 07:54:10 -0800 (PST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.206 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200702/198 X-Sequence-Number: 27532 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 From Bart.Degryse@indicator.be Mon Feb 19 16:05:27 2007 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

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@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__=--