pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProper case function
8+ messages / 7 participants
[nested] [flat]
* Proper case function
@ 2009-12-31 17:36 Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-12-31 17:40 ` Re: Proper case function Gary Chambers <gwchamb@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Gould @ 2009-12-31 17:36 UTC (permalink / raw)
To: pgsql-sql
All,
Has anyone got a proper case function that they are willing to share. We're
moving from SQL Anywhere to PostgreSQL and decided that much of our data is
upper case when it really should be proper cased. The columns that we will
be putting proper case data in are all defined using the contrib module
citext which makes searching very nice.
Best Regards
Michael Gould
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Proper case function
2009-12-31 17:36 Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
@ 2009-12-31 17:40 ` Gary Chambers <gwchamb@gmail.com>
2009-12-31 18:17 ` Re: Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
0 siblings, 1 reply; 8+ messages in thread
From: Gary Chambers @ 2009-12-31 17:40 UTC (permalink / raw)
To: Michael Gould <mgould@intermodalsoftwaresolutions.net>; +Cc: pgsql-sql
> Has anyone got a proper case function that they are willing to share. We're
> moving from SQL Anywhere to PostgreSQL and decided that much of our data is
> upper case when it really should be proper cased. The columns that we will
> be putting proper case data in are all defined using the contrib module
> citext which makes searching very nice.
Why doesn't the initcap function work as you require?
-- Gary Chambers
/* Nothing fancy and nothing Microsoft! */
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Proper case function
2009-12-31 17:36 Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-12-31 17:40 ` Re: Proper case function Gary Chambers <gwchamb@gmail.com>
@ 2009-12-31 18:17 ` Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-12-31 19:27 ` Re: Proper case function Tim Landscheidt <tim@tim-landscheidt.de>
2009-12-31 19:30 ` Re: Proper case function Seth Gordon <sethg@ropine.com>
2010-01-01 10:39 ` Re: Proper case function Jasen Betts <jasen@xnet.co.nz>
2010-01-06 15:42 ` Re: Proper case function John Summerfield <postgres@herakles.homelinux.org>
0 siblings, 4 replies; 8+ messages in thread
From: Michael Gould @ 2009-12-31 18:17 UTC (permalink / raw)
To: Gary Chambers <gwchamb@gmail.com>; +Cc: pgsql-sql
Gary,
Based on what I read it wouldn't handle cases where the result should be
MacDonald from macdonald. There are other cases such as the sentence below
('it''s crazy! i couldn''t believe kate mcdonald, leo dicaprio, (terrence)
trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to
[cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to
o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test)
This wouldn't handle the quotes and proper case all of the words.
Best Regards
Mike Gould
"Gary Chambers" <gwchamb@gmail.com> wrote:
>> Has anyone got a proper case function that they are willing to share.
We're
>> moving from SQL Anywhere to PostgreSQL and decided that much of our data
is
>> upper case when it really should be proper cased. The columns that we
will
>> be putting proper case data in are all defined using the contrib module
>> citext which makes searching very nice.
>
> Why doesn't the initcap function work as you require?
>
> -- Gary Chambers
>
> /* Nothing fancy and nothing Microsoft! */
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Proper case function
2009-12-31 17:36 Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-12-31 17:40 ` Re: Proper case function Gary Chambers <gwchamb@gmail.com>
2009-12-31 18:17 ` Re: Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
@ 2009-12-31 19:27 ` Tim Landscheidt <tim@tim-landscheidt.de>
3 siblings, 0 replies; 8+ messages in thread
From: Tim Landscheidt @ 2009-12-31 19:27 UTC (permalink / raw)
To: pgsql-sql
Michael Gould <mgould@intermodalsoftwaresolutions.net> wrote:
> Based on what I read it wouldn't handle cases where the result should be
> MacDonald from macdonald. There are other cases such as the sentence below
> ('it''s crazy! i couldn''t believe kate mcdonald, leo dicaprio, (terrence)
> trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to
> [cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to
> o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test)
> This wouldn't handle the quotes and proper case all of the words.
> [...]
Based on your requirements, you want to hire some cheap Eng-
lish native speaker with lots of cultural knowledge.
Tim
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Proper case function
2009-12-31 17:36 Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-12-31 17:40 ` Re: Proper case function Gary Chambers <gwchamb@gmail.com>
2009-12-31 18:17 ` Re: Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
@ 2009-12-31 19:30 ` Seth Gordon <sethg@ropine.com>
3 siblings, 0 replies; 8+ messages in thread
From: Seth Gordon @ 2009-12-31 19:30 UTC (permalink / raw)
To: pgsql-sql
Michael Gould wrote:
> Gary,
>
> Based on what I read it wouldn't handle cases where the result should be
>
> MacDonald from macdonald.
A function that translates “macdonald” to “MacDonald” would also
translate “mackintosh” (the raincoat) to “MacKintosh”, unless you write
it *really really* cleverly.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Proper case function
2009-12-31 17:36 Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-12-31 17:40 ` Re: Proper case function Gary Chambers <gwchamb@gmail.com>
2009-12-31 18:17 ` Re: Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
@ 2010-01-01 10:39 ` Jasen Betts <jasen@xnet.co.nz>
3 siblings, 0 replies; 8+ messages in thread
From: Jasen Betts @ 2010-01-01 10:39 UTC (permalink / raw)
To: pgsql-sql
On 2009-12-31, Michael Gould <mgould@intermodalsoftwaresolutions.net> wrote:
> Gary,
>
> Based on what I read it wouldn't handle cases where the result should be
>
> MacDonald from macdonald. There are other cases such as the sentence below
>
> ('it''s crazy! i couldn''t believe kate mcdonald, leo dicaprio, (terrence)
> trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to
> [cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to
> o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test)
>
> This wouldn't handle the quotes and proper case all of the words.
There is no case-restoring algorithm that works in all cases.
Some people case their name differently just as some spell their name
differently. Possibly for the same reasons.
Contact the owners of the names and offer them the opportunity to have
their name represented in the correct case - send them a link to an online
form.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Proper case function
2009-12-31 17:36 Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-12-31 17:40 ` Re: Proper case function Gary Chambers <gwchamb@gmail.com>
2009-12-31 18:17 ` Re: Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
@ 2010-01-06 15:42 ` John Summerfield <postgres@herakles.homelinux.org>
3 siblings, 0 replies; 8+ messages in thread
From: John Summerfield @ 2010-01-06 15:42 UTC (permalink / raw)
To: pgsql-sql
Michael Gould wrote:
> Gary,
>
> Based on what I read it wouldn't handle cases where the result should be
>
> MacDonald from macdonald. There are other cases such as the sentence below
I've looked at rationalising names in this manner before, and found
that, depending on the individual, both Macdonald and Macdonald are correct.
So I waved my hands around, harrumphed and went for Macdonald in all cases.
I'm sure that should the MacDonalds have not become accustomed to being
recorded as Macdonalds, surely they will soon, or at least not complain
about it.
--
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Proper case function
@ 2010-09-23 14:13 Jonathan Brinkman <JB@BlackSkyTech.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jonathan Brinkman @ 2010-09-23 14:13 UTC (permalink / raw)
To: mgould@intermodalsoftwaresolutions.net; +Cc: pgsql-sql
Here is a simple title-case function for Postgresql.
Best,
Jonathan
CREATE OR REPLACE FUNCTION "format_titlecase" (
"v_inputstring" varchar
)
RETURNS varchar AS
$body$
/*
select * from Format_TitleCase('MR DOG BREATH');
select * from Format_TitleCase('each word, mcclure of this string:shall be
transformed');
select * from Format_TitleCase(' EACH WORD HERE SHALL BE TRANSFORMED TOO
incl. mcdonald o''neil o''malley mcdervet');
select * from Format_TitleCase('mcclure and others');
select * from Format_TitleCase('J & B ART');
select * from Format_TitleCase('J&B ART');
select * from Format_TitleCase('J&B ART J & B ART this''s art''s house''s
problem''s 0''shay o''should work''s EACH WORD HERE SHALL BE TRANSFORMED
TOO incl. mcdonald o''neil o''malley mcdervet');
*/
DECLARE
v_Index INTEGER;
v_Char CHAR(1);
v_OutputString VARCHAR(4000);
SWV_InputString VARCHAR(4000);
BEGIN
SWV_InputString := v_InputString;
SWV_InputString := LTRIM(RTRIM(SWV_InputString)); --cures problem where
string starts with blank space
v_OutputString := LOWER(SWV_InputString);
v_Index := 1;
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,1,1)) from 1 for 1); -- replaces 1st char of
Output with uppercase of 1st char from Input
WHILE v_Index <= LENGTH(SWV_InputString) LOOP
v_Char := SUBSTR(SWV_InputString,v_Index,1); -- gets loop's working
character
IF v_Char IN('m','M','
',';',':','!','?',',','.','_','-','/','&','''','(',CHR(9)) then
--END4
IF v_Index+1 <= LENGTH(SWV_InputString) then
IF v_Char = '''' AND UPPER(SUBSTR(SWV_InputString,v_Index+1,1))
<> 'S' AND SUBSTR(SWV_InputString,v_Index+2,1) <> REPEAT(' ',1) then -- if
the working char is an apost and the letter after that is not S
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
ELSE
IF v_Char = '&' then -- if the working char is an &
IF(SUBSTR(SWV_InputString,v_Index+1,1)) = ' ' then
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+2,1)) from v_Index+2 for 1);
ELSE
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
END IF;
ELSE
IF UPPER(v_Char) != 'M' AND
(SUBSTR(SWV_InputString,v_Index+1,1) <> REPEAT(' ',1) AND
SUBSTR(SWV_InputString,v_Index+2,1) <> REPEAT(' ',1)) then
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
END IF;
END IF;
END IF;
-- special case for handling "Mc" as
in McDonald
IF UPPER(v_Char) = 'M' AND
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) = 'C' then
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index,1)) from v_Index for 1);
--MAKES THE C LOWER
CASE.
v_OutputString := OVERLAY(v_OutputString placing
LOWER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
-- makes the letter
after the C UPPER case
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+2,1)) from v_Index+2 for 1);
--WE TOOK CARE OF
THE CHAR AFTER THE C (we handled 2 letters instead of only 1 as usual), SO
WE NEED TO ADVANCE.
v_Index := v_Index+1;
END IF;
END IF;
END IF; --END3
v_Index := v_Index+1;
END LOOP; --END2
RETURN coalesce(v_OutputString,'');
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-09-23 14:13 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-12-31 17:36 Proper case function Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-12-31 17:40 ` Gary Chambers <gwchamb@gmail.com>
2009-12-31 18:17 ` Michael Gould <mgould@intermodalsoftwaresolutions.net>
2009-12-31 19:27 ` Tim Landscheidt <tim@tim-landscheidt.de>
2009-12-31 19:30 ` Seth Gordon <sethg@ropine.com>
2010-01-01 10:39 ` Jasen Betts <jasen@xnet.co.nz>
2010-01-06 15:42 ` John Summerfield <postgres@herakles.homelinux.org>
2010-09-23 14:13 Re: Proper case function Jonathan Brinkman <JB@BlackSkyTech.com>
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