嗨,正如标题中所述,我的名字格式为LASTNAME、FIRSTNAME,我希望数据格式为FIRSTNAME LASTNAME。它将用于SSIS中的OLE DB SOURCE查询,因此不希望在函数中使用它。如何做到这一点?
declare @NameString Varchar(50) = 'BLOGGS, JOE'
SELECT CASE WHEN CHARINDEX(',',@NameString) = 0 THEN 'Unexpected format: ' + @NameString ELSE
UPPER(LEFT( RIGHT( @NameString,len(@NameString)-CHARINDEX(',',@NameString)-1 ),1)) + --First Initial
LOWER(SUBSTRING(@NameString,CHARINDEX(',',@NameString)+3,LEN(@NameString))) -- rest of first name
+' ' +
UPPER(LEFT(@NameString,1)) -- Surname initial
+ LOWER(RIGHT(LEFT(@NameString,CHARINDEX(',',@NameString)-1),LEN(LEFT(@NameString,CHARINDEX(',',@NameString)-1))-1)) -- rest of surname
END
如何将名字转换为camelcase?你怎么知道大写字母放在哪里?如果FIRSTNAME是JOE,那么它的名字是什么?乔?如果这就是你的意思,那么你可以做:
SELECT UPPER(LEFT(FIRSTNAME,1))+LOWER(SUBSTRING(FIRSTNAME,2,LEN(FIRSTNAME))) FROM ....
另一个版本,但功能较少:
DECLARE @Name VARCHAR( 100 )
SET @Name = 'LASTNAME, FIRSTNAME'
SELECT
-- First Name
UPPER( SUBSTRING( @Name, CHARINDEX( ',', @Name ) + 2, 1 ))
+ LOWER( SUBSTRING( @Name, CHARINDEX( ',', @Name ) + 3, 1000 ))
+ ' '
-- Last Name
+ UPPER( LEFT( @Name, 1 ))
+ LOWER( SUBSTRING( @Name, 2, CHARINDEX( ',', @Name ) - 2 ))
如果有任何区别的话,看看它与JamieA在性能方面的比较会很有趣。