获取SQL字符串中每个单词的第一个字母 [英] Get the first letter of each word in a SQL string
本文介绍了获取SQL字符串中每个单词的第一个字母的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
可能存在重复:
SQL来挑选一个人名字符串并输出缩写字母
在 MS-SQL Server
中,有一种方法可以获取第一个字母字符串中的每个单词的?例如:
姓名:
Michael Joseph Jackson
查询:
SELECT name,[function] as initial FROM Customers
结果:
MJJ
解决方案
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 Jackson');
SELECT dbo.fnFirsties('Michael Joseph Jackson'); - 多重空间保护:)
结果:
MJJ
MJJ
Possible Duplicate:
sql to pick apart a string of a persons name and output the initials
In MS-SQL Server
, there is a way to get the first letter of each word in a string? For example:
Name:
Michael Joseph Jackson
Query:
SELECT name, [function] as initial FROM Customers
Result:
MJJ
解决方案
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 Jackson');
SELECT dbo.fnFirsties(' Michael Joseph Jackson '); -- multiple space protection :)
Results:
MJJ
MJJ
这篇关于获取SQL字符串中每个单词的第一个字母的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文