postgresql 将 postgres 中的所有记录转换为 Titlecase，首字母大写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6176057/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert all records in postgres to Titlecase, first letter uppercase
提问by topless
I have a simple table in PostgreSQL called keywords with a simple text field called name. I want to convert all names of keywords in first letter uppercase. Is there a way to do it from psql console?
我在 PostgreSQL 中有一个简单的表，称为关键字，带有一个名为 name 的简单文本字段。我想将所有关键字名称转换为首字母大写。有没有办法从 psql 控制台做到这一点？
回答by Denis de Bernardy
There is an initcap()function, if you're meaning to uppercase the first letter of each keyword and to lowercase the following characters:
有一个initcap()函数，如果您要大写每个关键字的第一个字母并将以下字符小写：
update foo
set bar = initcap(bar)
Else combine substring()and upper():
否则结合substring()和upper()：
update foo
set bar = upper(substring(bar from 1 for 1)) ||
substring(bar from 2 for length(bar))
http://www.postgresql.org/docs/current/static/functions-string.html
http://www.postgresql.org/docs/current/static/functions-string.html
回答by Scott Pritchett
select initcap('r. lópez vi?a tondonia Rioja White Vi?a');
This does give the correct answer (R. López Vi?a Tondonia Rioja White Vi?a) in our version of Postgres (9.0.7).
这确实在我们的 Postgres (9.0.7) 版本中给出了正确答案 (R. López Vi?a Tondonia Rioja White Vi?a)。
回答by Garret K
The initcap function capitalizes letters after special characters (dashes, apostrophes, etc). I only want to capitalize after a space.
initcap 函数将特殊字符（破折号、撇号等）后面的字母大写。我只想在一个空格后大写。
Similar to Denis' answer, this function will convert the first letter of every word (separated by a space)
与 Denis 的回答类似，此函数将转换每个单词的第一个字母（以空格分隔）
CREATE OR REPLACE FUNCTION titlecase(instr varchar) RETURNS varchar AS $$
DECLARE
strarray varchar[] := string_to_array(inStr, ' ');
outstrarray varchar[];
word varchar;
BEGIN
FOREACH word IN ARRAY strarray
LOOP
outstrarray := array_append(outstrarray, (upper(left(word,1))::varchar ||
lower(right(word,-1))::varchar)::varchar);
END LOOP;
RETURN array_to_string(outstrarray,' ','');
END;
$$ LANGUAGE 'plpgsql';
回答by MD Shahrouq
@denis, Gave the Right Answer!
@denis，给出正确答案！
But in my case I use PgAdmin3 , so after selecting the database there is SQL query Options , So there we can directly add the above query in it.
但是在我的例子中我使用 PgAdmin3 ，所以在选择数据库后有 SQL query Options ，所以我们可以直接在其中添加上述查询。
I had a table called subcategory_subcategory(name of table) in that i wanted to change a column values whose name was Item_name(name of column ) , so my query was like this
我有一个名为 subcategory_subcategory(name of table) 的表，因为我想更改名称为 Item_name(name of column ) 的列值，所以我的查询是这样的
update subcategory_subcategory
set Item_name = initcap(Item_name)
