获取 SQL 字符串中每个单词的第一个字母
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9301081/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the first letter of each word in a SQL string
提问by Aldonas
Possible Duplicate:
sql to pick apart a string of a persons name and output the initials
可能的重复：
sql 来挑选一串人名并输出首字母
In MS-SQL Server, there is a way to get the first letter of each word in a string? For example:
在 中MS-SQL Server，有没有办法获取字符串中每个单词的第一个字母？例如：
Name:
姓名：
Michael Joseph Hymanson
迈克尔·约瑟夫·Hyman逊
Query:
询问：
SELECT name, [function] as initial FROM Customers
Result:
结果：
MJJ
麻省理工学院
回答by John Dewey
This function will shield your results against multiple sequential spaces in the source string:
此函数将针对源字符串中的多个连续空格屏蔽您的结果：
CREATE FUNCTION dbo.fnFirsties ( @str NVARCHAR(4000) )
RETURNS NVARCHAR(2000)
AS
BEGIN
DECLARE @retval NVARCHAR(2000);
SET @str=RTRIM(LTRIM(@str));
SET @retval=LEFT(@str,1);
WHILE CHARINDEX(' ',@str,1)>0 BEGIN
SET @str=LTRIM(RIGHT(@str,LEN(@str)-CHARINDEX(' ',@str,1)));
SET @retval+=LEFT(@str,1);
END
RETURN @retval;
END
GO
SELECT dbo.fnFirsties('Michael Joseph Hymanson');
SELECT dbo.fnFirsties(' Michael Joseph Hymanson '); -- multiple space protection :)
Results:
结果：
MJJ
MJJ
回答by Carth
Assuming we're doing this in MSSQL2008R2 though nothing involved should really matter here. All we do is have some fun with string manipulation. You could put this into a funciton or proc or just run it in query analyzer directly.
假设我们在 MSSQL2008R2 中执行此操作，尽管这里不涉及任何实际问题。我们所做的就是在字符串操作中获得一些乐趣。您可以将其放入函数或过程中，或者直接在查询分析器中运行它。
DECLARE @str varchar(250) = 'Michael Joseph Hymanson'
DECLARE @initials varchar(250) = substring(@str,1,1)
WHILE(charindex(' ',@str)!=0)
BEGIN
DECLARE @currentSpace int = charindex(' ',@str)
SET @initials += substring(@str,@currentSpace+1,1)
SET @str = substring(@str,@currentSpace+1,len(@str))
END
SELECT @initials
If you're not doing this for some trivial purpose you'll likely want to clean up the data before attempting to process it. Names are often prefixed by titles, data entry fields are susceptible to user error, etc.
如果您不是为了一些微不足道的目的而这样做，您可能希望在尝试处理数据之前清理数据。名称通常以标题为前缀，数据输入字段易受用户错误等影响。
回答by Andrew
You'll want to add some checks and error handling before you update tblStudents or something, but this should get you started.
在更新 tblStudents 或其他内容之前，您需要添加一些检查和错误处理，但这应该可以帮助您入门。
CREATE FUNCTION initials ( @s AS nvarchar(4000))
RETURNS nvarchar(100)
AS
BEGIN
DECLARE @i nvarchar(100) = LEFT(@s, 1); -- first char in string
DECLARE @p int = CHARINDEX(' ', @s); -- location of first space
WHILE (@p > 0) -- while a space has been found
BEGIN
SET @i = @i + SUBSTRING(@s, @p + 1, 1) -- add char after space
SET @p = CHARINDEX(' ', @s, @p + 1); -- find next space
END
RETURN @i
END
GO
SELECT dbo.initials('Michael Joseph Hymanson');
回答by Diego
You first need a table-valued function that splits a varcharand returns a table with a single-column called 'S'.
您首先需要一个表值函数，用于拆分 avarchar并返回一个带有名为“S”的单列的表。
CREATE FUNCTION dbo.fn_Split2 (@sep nvarchar(10), @s nvarchar(4000))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + (datalength(@sep)/2), CHARINDEX(@sep, @s, stop + (datalength(@sep)/2))
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s
FROM Pieces
)
Getting the initials is easy now:
现在获取首字母很容易：
DECLARE @Initials VARCHAR(8000)
SELECT @Initials = COALESCE(@Initials, '') + SUBSTRING(s, 1, 1) FROM dbo.fn_Split2(' ', 'Michael Joseph Hymanson')
SELECT @Initials
That returns 'MJJ', as required.
根据需要返回“MJJ”。
回答by Kogitsune
SUBSTRING( string, startpos, endpos ) AS 'Initial'
SUBSTRING(string, startpos, endpos) AS 'Initial'
