SQL:仅大写第一个字母 [英] SQL: capitalize first letter only

问题描述

我需要一个 SQL 语句来将每个单词的第一个字母大写.其他字符必须小写.

I need an SQL statement to capitalize the first letter of each word. The other characters have to be lower case.

单词可以是这样的:

wezembeek-oppem
roeselare
BRUGGE
louvain-la-neuve

必须是:

Wezembeek-Oppem
Roeselare
Brugge
Louvain-La-Neuve

这个应该是用UPDATE语句，我要更新一个列的数据.非常感谢您提前回答，我是 SQL 新手.

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.

推荐答案

您是要求重命名列本身还是将列内的数据大写?如果您必须更改其数据，请使用以下命令:

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]

希望这会有所帮助.

我意识到了-"，所以这是我在函数中解决这个问题的尝试.

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

那么代码将是:

UPDATE [yourtable]
SET word=dbo.CapitalizeFirstLetter([STRING TO GO HERE])

这篇关于SQL:仅大写第一个字母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！