我需要一个SQL语句,将每个单词的第一个字母大写。其他字符必须是小写。
这些词可以是这样的。
wezembeek-oppem
roeselare
BRUGGE
louvain-la-neuve
这就得了。
Wezembeek-Oppem
Roeselare
Brugge
Louvain-La-Neuve
这应该是一个UPDATE语句,我想更新一个列的数据。 非常感谢您的回答,我是一个SQL新手。
你是要求对列本身进行重命名还是对列内的数据进行大写?如果你要改变的是数据,那就用这个。
UPDATE [yourtable]
SET word=UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word)))
如果你只是想改变它的显示方式,而不需要改变表中的实际数据。
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])
创建以下函数
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 ;
然后像这样调用它
select dbo.InitialCap(columnname) from yourtable
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));