sql to pick apart a string of a persons name and output the initials
how can i get SQL to take a sting and return the first letter of each word passed into it.
I wan开发者_如何转开发t to use this UDF for generating initials for peoples names I have in the DB.
names can be 2 (fname, lname)or 3(...mname) words
i am using sql2005
This should work for both "Firstname Lastname" and "Firstname Middlename Lastname" combinations.
DECLARE @name AS NVARCHAR(50)
SET @name = 'Firstname Middle Lastname'
SELECT SUBSTRING(@name, 1, 1) + --First initial
SUBSTRING(@name, CHARINDEX(' ', @name) + 1, 1) + --Middle/Last initial
CASE WHEN 0 <> CHARINDEX(' ', @name, CHARINDEX(' ', @name) + 1) -- More than two words
THEN SUBSTRING(@name, CHARINDEX(' ', @name, CHARINDEX(' ', @name) + 1) + 1, 1) --Last initial
ELSE '' --Have to add empty string to avoid NULLing entire result
END
Of course, if users have a space in one of their names for some reason you will have an issue parsing this out, but I suspect that would be the case anyways when not storing your names in separate fields.
For SQL Server 2017 and newer:
CREATE FUNCTION dbo.fnGetFirstChars (@string NVARCHAR(max), @seperator NVARCHAR(MAX))
RETURNS NVARCHAR(max)
AS BEGIN
DECLARE @result NVARCHAR(MAX)
SELECT @result = STRING_AGG(SUBSTRING(value, 1, 1), '')
FROM STRING_SPLIT(@string, @seperator)
RETURN @result
END;
CREATE FUNCTION dbo.GetFirstLetter ( @Array VARCHAR(1000), @separator VARCHAR(10))
RETURNS @resultTable TABLE
(parseValue VARCHAR(100))
AS
BEGIN
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(1000)
SET @array = @array + @separator
WHILE patindex('%' + @separator + '%' , @array) <> 0
BEGIN
SELECT @separator_position = patindex('%' + @separator + '%', @array)
SELECT @array_value = left(@array, @separator_position - 1)
INSERT @resultTable
VALUES (SUBSTRING(Cast(@array_value AS varchar), 1, 1))
SELECT @array = stuff(@array, 1, @separator_position, '')
END
RETURN
END
Here's my solution, and it has these features/peculiarities:
- It can process however many names there are in the string. (That is, both less than two and more than three.)
- All spaces between the names are preserved.
I know the OP has specified that there can only be 2 or 3 names in his case. I don't mind. I'm just sharing a solution that works, and if it's not best for the particular problem, it's fine.
So, here's the function:
CREATE FUNCTION dbo.fnGetInitials (@name varchar(max))
RETURNS varchar(max)
AS BEGIN
DECLARE @cutpos int, @spacepos int, @result varchar(max);
DECLARE @cutlist TABLE (CutPos int, SpacePos int);
SET @result = LTRIM(RTRIM(@name));
SET @cutpos = 2;
SET @spacepos = CHARINDEX(' ', @result);
WHILE @spacepos > 0 BEGIN
INSERT INTO @cutlist VALUES (@cutpos, @spacepos);
SET @spacepos = @spacepos + 1;
SET @cutpos = @spacepos + 1;
SET @spacepos = CHARINDEX(' ', @result, @spacepos);
END;
DELETE FROM @cutlist WHERE CutPos >= SpacePos;
SELECT @result = STUFF(@result, CutPos, SpacePos - CutPos, '')
FROM @cutlist
ORDER BY CutPos DESC;
RETURN @result;
END;
And here's a test call:
SELECT dbo.fnGetInitials(' John Ronald Reuel Tolkien ');
and the result:
----------------------------------------------------------------------------------------------------
J R R Tolkien
You can achieve it via xquery as well.
Declare @Xml XML
Declare @String Varchar(Max)
Declare @firstletter Varchar(Max)
Declare @delimiter Varchar(5)
SET @delimiter=' '
SET @String= 'THIS IS SQL'
SET @Xml = cast(('<a>'+replace(@String,@delimiter,'</a><a>')+'</a>') AS XML)
;WITH CTE AS
(SELECT A.value('.', 'varchar(max)') as [Column]FROM @Xml.nodes('a') AS FN(a) )
SELECT Stuff((SELECT '' + LEFT([Column],1)from CTE
FOR XML PATH ('') ),1,0,'')
Here is the complete solution.
http://raresql.com/2013/04/12/sql-server-get-the-first-letter-of-each-word-in-a-string-column/
A Picture is 100 times better than description. Here is an example of UDF declaration:
CREATE FUNCTION dbo.GetOnlyFirstLetters(@str NVARCHAR(4000),@sep NVARCHAR(10) )
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @textXML XML
SELECT @textXML = CAST('<d>' + replace(@str, @sep, '</d><d>') + '</d>' AS XML)
DECLARE @result VARCHAR(8000)
SET @result = ''
SELECT @result = @result + LEFT(T.split.value ('.', 'nvarchar(max)'), 1)
FROM @textXML.nodes ('/d') T (split)
RETURN @result
END
GO
Here is how to call:
SELECT dbo.GetOnlyFirstLetters('Humayoun Kabir Sohel',' ');
Result will be:
HKS
精彩评论