怎么把某字段单词的首字母改为大写其它小写？

 

要改某字段的内容，要求改为大写的情况：
1、字段首字母
2、空格后的首字母
3、“-”后的首字母
其它都改为小写。

 

例如有表
id    name
1    aBC EFG
2    hij-klm
3    NOPQ

我需要的结果：
id    name
1    Abc Efg
2    Hij-Klm
3    Nopq

---------------------------------
--  Author: liangCK 小梁
---------------------------------

--> 生成测试数据: @T
DECLARE @T TABLE (id INT,name VARCHAR(50))
INSERT INTO @T
SELECT 1,'aBC EFG' UNION ALL
SELECT 2,'hij-klm' UNION ALL
SELECT 3,'NOPQ'

--SQL查询如下:

;WITH Liang AS
(
   SELECT
        M.id,
        SUBSTRING(M.name,N.number,1) AS s,
        number
   FROM @T AS M
        JOIN master.dbo.spt_values AS N
            ON number BETWEEN 1 AND LEN(M.name)
               AND N.type='p'
)
UPDATE A SET
     name=B.string.value('.','VARCHAR(max)')
FROM @T AS A
   CROSS APPLY (
       SELECT string=(
                  SELECT
                      CASE WHEN number=1 OR
                           (SELECT s
                            FROM Liang
                            WHERE id=T.idAND number=T.number-1) IN(' ','-')
                             THEN UPPER(s)
                          ELSE LOWER(s) END
                 FROM Liang AS T
                 WHERE id=A.id
                 FOR XML PATH(''),TYPE
              )
   ) AS B

SELECT * FROM @t

/*
id          name
----------- --------------------------------------------------
1           Abc Efg
2           Hij-Klm
3           Nopq

(3 行受影响)
*/

 

 

 

------------------------------------------------------------------------
-- Author:  happyflystone 
-- Date  :  2009-03-16 10:35:16
-- Ver:     Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
--          Apr 14 2006 01:12:25
--          Copyright (c) 1988-2005 Microsoft Corporation
--          Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--     
------------------------------------------------------------------------

-- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
    DROP TABLE ta
Go
CREATE TABLE ta(id INT,name NVARCHAR(7))
Go
INSERT INTO ta
SELECT 1,'aBC' UNION ALL
SELECT 2,'hij-klm' UNION ALL
SELECT 3,'NOPQ'
GO
--Start
create function f_s(@s nvarchar(20))
returns varchar(20)
as
begin
    set @s  = lower(@s)
    declare @I int
    set @I = 1
    while right(left(@s,@i),1) in (' ','-')
        set @I = @I + 1
    return left(@s,@I -1) + char(ascii(substring(@s,@i,1))- 32)+ right(@s,len(@s) -@i)
end
go
SELECT
    *,dbo.f_s(name)
FROM
    TA
drop function f_s


--Result:
/*
id          name   
----------- ------- --------------------
1           aBC     Abc
2           hij-klm Hij-klm
3           NOPQ    Nopq


*/
--End