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

    技术2022-05-11  19

    要改某字段的内容,要求改为大写的情况: 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 小梁-----------------------------------> 生成测试数据: @TDECLARE @T TABLE (id INT,name VARCHAR(50))INSERT INTO @TSELECT 1,'aBC EFG' UNION ALLSELECT 2,'hij-klm' UNION ALLSELECT 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 BSELECT * FROM @t/*id          name----------- --------------------------------------------------1           Abc Efg2           Hij-Klm3           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: taIF OBJECT_ID('ta') IS NOT NULL     DROP TABLE taGoCREATE TABLE ta(id INT,name NVARCHAR(7))GoINSERT INTO taSELECT 1,'aBC' UNION ALLSELECT 2,'hij-klm' UNION ALLSELECT 3,'NOPQ' GO--Startcreate function f_s(@s nvarchar(20))returns varchar(20)asbegin    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)endgoSELECT     *,dbo.f_s(name) FROM    TA drop function f_s--Result:/*id          name    ----------- ------- --------------------1           aBC     Abc2           hij-klm Hij-klm3           NOPQ    Nopq*/--End


    最新回复(0)