Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
5.9k views
in Technique[技术] by (71.8m points)

sql server 2005 - 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 want 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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...