要改某字段的内容,要求改为大写的情况: 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