SQL：仅将首字母大写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15290754/
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
SQL: capitalize first letter only
提问by samn
I need an SQL statement to capitalize the first letter of each word. The other characters have to be lower case.
我需要一个 SQL 语句来将每个单词的第一个字母大写。其他字符必须小写。
The words can be like this:
这些词可以是这样的：
wezembeek-oppem
roeselare
BRUGGE
louvain-la-neuve
Which would have to be:
必须是：
Wezembeek-Oppem
Roeselare
Brugge
Louvain-La-Neuve
This should be with an UPDATE statement, I want to update the data of a column. Thank you very much for your answers in advance, I'm an SQL novice.
这应该是一个UPDATE语句，我想更新一个列的数据。非常感谢您提前回答，我是 SQL 新手。
回答by Scott Sellers
Are you asking for renaming column itself or capitalise the data inside column? If its data you've to change, then use this:
您是要求重命名列本身还是将列内的数据大写？如果您必须更改其数据，请使用以下命令：
UPDATE [yourtable]
SET word=UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word)))
If you just wanted to change it only for displaying and do not need the actual data in table to change:
如果您只想更改它只是为了显示而不需要更改表中的实际数据：
SELECT UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word))) FROM [yourtable]
Hope this helps.
希望这可以帮助。
EDIT: I realised about the '-' so here is my attempt to solve this problem in a function.
编辑：我意识到了“-”，所以这是我在函数中解决这个问题的尝试。
CREATE FUNCTION [dbo].[CapitalizeFirstLetter]
(
--string need to format
@string VARCHAR(200)--increase the variable size depending on your needs.
)
RETURNS VARCHAR(200)
AS
BEGIN
--Declare Variables
DECLARE @Index INT,
@ResultString VARCHAR(200)--result string size should equal to the @string variable size
--Initialize the variables
SET @Index = 1
SET @ResultString = ''
--Run the Loop until END of the string
WHILE (@Index <LEN(@string)+1)
BEGIN
IF (@Index = 1)--first letter of the string
BEGIN
--make the first letter capital
SET @ResultString =
@ResultString + UPPER(SUBSTRING(@string, @Index, 1))
SET @Index = @Index+ 1--increase the index
END
-- IF the previous character is space or '-' or next character is '-'
ELSE IF ((SUBSTRING(@string, @Index-1, 1) =' 'or SUBSTRING(@string, @Index-1, 1) ='-' or SUBSTRING(@string, @Index+1, 1) ='-') and @Index+1 <> LEN(@string))
BEGIN
--make the letter capital
SET
@ResultString = @ResultString + UPPER(SUBSTRING(@string,@Index, 1))
SET
@Index = @Index +1--increase the index
END
ELSE-- all others
BEGIN
-- make the letter simple
SET
@ResultString = @ResultString + LOWER(SUBSTRING(@string,@Index, 1))
SET
@Index = @Index +1--incerase the index
END
END--END of the loop
IF (@@ERROR
<> 0)-- any error occur return the sEND string
BEGIN
SET
@ResultString = @string
END
-- IF no error found return the new string
RETURN @ResultString
END
So then the code would be:
那么代码将是：
UPDATE [yourtable]
SET word=dbo.CapitalizeFirstLetter([STRING TO GO HERE])
回答by DevelopmentIsMyPassion
Create the below function
创建以下函数
Alter FUNCTION InitialCap(@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Position INT;
SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin);
WHILE @Position > 0
SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin);
RETURN @String;
END ;
Then call it like
然后称之为
select dbo.InitialCap(columnname) from yourtable
回答by TechDo
Please check the query without using a function:
请在不使用函数的情况下检查查询：
declare @T table(Insurance varchar(max))
insert into @T values ('wezembeek-oppem')
insert into @T values ('roeselare')
insert into @T values ('BRUGGE')
insert into @T values ('louvain-la-neuve')
select (
select upper(T.N.value('.', 'char(1)'))+
lower(stuff(T.N.value('.', 'varchar(max)'), 1, 1, ''))+(CASE WHEN RIGHT(T.N.value('.', 'varchar(max)'), 1)='-' THEN '' ELSE ' ' END)
from X.InsXML.nodes('/N') as T(N)
for xml path(''), type
).value('.', 'varchar(max)') as Insurance
from
(
select cast('<N>'+replace(
replace(
Insurance,
' ', '</N><N>'),
'-', '-</N><N>')+'</N>' as xml) as InsXML
from @T
) as X
回答by Aspirant
select replace(wm_concat(new),',','-') exp_res from (select distinct initcap(substr(name,decode(level,1,1,instr(name,'-',1,level-1)+1),decode(level,(length(name)-length(replace(name,'-','')))+1,9999,instr(name,'-',1,level)-1-decode(level,1,0,instr(name,'-',1,level-1))))) new from table;
connect by level<= (select (length(name)-length(replace(name,'-','')))+1 from table));
