这在MSSQL中。我需要生成缩写。源数据没有缩写，因此我想从名字中生成姓名缩写。
Firstname(源数据(是100个字符。我将其传递给缩短它并吐出50个字符的函数。首字母的目标字段是最大15个字符，因此我抓住LEFT(..., 15)。
我创建了一个函数来处理＆amp;但是，逗号取决于＆amp;和逗号，功能的结果掉了字母。
CREATE FUNCTION [dbo].[fn_Strip_to_Initials_failed_Try1]
(@string NVARCHAR(100))
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @retrieval NVARCHAR(100);
SET @string = RTRIM(LTRIM(@string));
SET @retrieval = LEFT(@string, 1);
-- Step 1: get non space character after & symbol
WHILE CHARINDEX('&', @string, 1) > 0
BEGIN
SET @string = LTRIM(RIGHT(@string, LEN(@string) - CHARINDEX('&', @string, 1)));
SET @retrieval += '&' + LEFT(@string, 1);
END
-- Step 2: get non space character after commas
WHILE CHARINDEX(',', @string, 1) > 0
BEGIN
SET @string = LTRIM(RIGHT(@string, LEN(@string) - CHARINDEX(',', @string, 1)));
SET @retrieval += '&' + LEFT(@string, 1);
END
-- Step 4: get non space character after /
WHILE CHARINDEX('/', @string, 1) > 0
BEGIN
SET @string = LTRIM(RIGHT(@string, LEN(@string) - CHARINDEX('/', @string, 1)));
SET @retrieval += '&' + LEFT(@string, 1);
END
-- Step 8: uppercase the string
SET @retrieval = UPPER(@retrieval);
RETURN @retrieval;
END
GO;
SELECT
LEFT(REPLACE(REPLACE(dbo.fn_Strip_to_Initials('Bob & Marline'),'&', ' & '),',',', '), 15),
LEFT(REPLACE(REPLACE(dbo.fn_Strip_to_Initials('Bob , Marline'),'&', ' & '),',',', '),15),
LEFT(REPLACE(REPLACE(dbo.fn_Strip_to_Initials('Bob & Marline & John'), '&', ' & '), ',', ', '), 15),
LEFT(REPLACE(REPLACE(dbo.fn_Strip_to_Initials('Bob , Marline , John'),'&', ' & '),',',', '), 15),
LEFT(REPLACE(REPLACE(dbo.fn_Strip_to_Initials('Bob & Marline , John'),'&', ' & '),',',', '),15),
LEFT(REPLACE(REPLACE(dbo.fn_Strip_to_Initials('Bob , Marline & John'),'&', ' & '),',',', '),15)
我期望以下结果：
B & M
B & M
B & M & J
B & M & J
B & M & J
B & M & J
实际结果：
B & M
B & M
B & M & J
B & M & J
B & M & J
B & J
----------
--04/05/2019 - see below for solution (I have made a modification to John Cappelletti's solution)
------------
--note - the below is programmed to return up to 7 characters. Including 1st charater + each other character will take up 4 characters. EG ' & A' initial consumes 4 characters.
-- 1 + (4*3)= 13 characters. Trying to return 15 charaters would bring me the data:
--'A & B & C & D &'
--in my scenario, the field only accepts 15 characters into 'initials'
--secondly, I want to NOT create initials when there are Firstnames like 'Bob And Jane' hence the CASE
--so I am calling the function with:
CASE
WHEN LEN(LTRIM(RTRIM(FIRSTNAME))) = 0 THEN NULL
WHEN LTRIM(RTRIM(FIRSTNAME)) LIKE '% %'
AND LTRIM(RTRIM(FIRSTNAME)) NOT LIKE '%&%'
AND LTRIM(RTRIM(FIRSTNAME)) NOT LIKE '%,%'
AND LTRIM(RTRIM(FIRSTNAME)) NOT LIKE '%/%'
AND LTRIM(RTRIM(FIRSTNAME)) NOT LIKE '%%'
THEN NULL
ELSE LEFT(dbo.fn_Strip_to_Initials(FIRSTNAME),13)
END AS Initials
--
--
CREATE FUNCTION [dbo].[fn_Strip_to_Initials] (@string NVARCHAR(100))
RETURNS NVARCHAR(50)
AS
BEGIN
RETURN (
SELECT CONCAT(LEFT(Pos1,1)
,' & ' +LEFT(Pos2,1)
,' & ' +LEFT(Pos3,1)
,' & ' +LEFT(Pos4,1)
,' & ' +LEFT(Pos5,1)
,' & ' +LEFT(Pos6,1)
,' & ' +LEFT(Pos7,1)
)
FROM (VALUES ( UPPER ( REPLACE(
REPLACE(
REPLACE(@string,',','&')
,'/','&')
,'','&')
) )
)A(S)
CROSS APPLY (
SELECT Pos1 = LTRIM(RTRIM(xDim.value('/x[1]','varchar(max)')))
,Pos2 = LTRIM(RTRIM(xDim.value('/x[2]','varchar(max)')))
,Pos3 = LTRIM(RTRIM(xDim.value('/x[3]','varchar(max)')))
,Pos4 = LTRIM(RTRIM(xDim.value('/x[4]','varchar(max)')))
,Pos5 = LTRIM(RTRIM(xDim.value('/x[5]','varchar(max)')))
,Pos6 = LTRIM(RTRIM(xDim.value('/x[6]','varchar(max)')))
,Pos7 = LTRIM(RTRIM(xDim.value('/x[7]','varchar(max)')))
FROM ( VALUES (CAST('<x>' + REPLACE((SELECT REPLACE(S,'&','§§Split§§') AS [*] FOR XML PATH('')),'§§Split§§','</x><x>')+'</x>' AS XML))) A(xDim)
) B
)
END
这是使用CROSS APPLY和一个小XML的一个选项。如果确实需要，可以将其转换为UDF
示例
Declare @YourTable table (id int, FullName varchar(150))
Insert Into @YourTable values
(1,'Bob & Marline')
,(2,'Bob & Marline & John')
,(3,'Bob , Marline & John')
Select A.*
,Initial = concat(left(Pos1,1)
,' & ' +left(Pos2,1)
,' & ' +left(Pos3,1)
,' & ' +left(Pos4,1)
,' & ' +left(Pos5,1)
,' & ' +left(Pos6,1)
,' & ' +left(Pos7,1)
)
From @YourTable A
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
From ( values (cast('<x>' + replace((Select replace(replace(FullName,',','&'),'&','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml))) A(xDim)
) B
返回
id FullName Initial
1 Bob & Marline B & M
2 Bob & Marline & John B & M & J
3 Bob , Marline & John B & M & J -- added and corrected
编辑 - 作为函数
CREATE FUNCTION [dbo].[fn_Strip_to_Initials] (@string NVARCHAR(100))
RETURNS NVARCHAR(50)
AS
BEGIN
Return (
Select concat(left(Pos1,1)
,' & ' +left(Pos2,1)
,' & ' +left(Pos3,1)
,' & ' +left(Pos4,1)
,' & ' +left(Pos5,1)
,' & ' +left(Pos6,1)
,' & ' +left(Pos7,1)
)
From (values ( replace(
replace(
replace(@string,',','&')
,'/','&')
,'','&')
)
)A(S)
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
From ( values (cast('<x>' + replace((Select replace(S,'&','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml))) A(xDim)
) B
)
End
用法
Select [dbo].[fn_Strip_to_Initials]( 'Bob & Marline John')
返回
B & M & J