Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Les.61
Starting Member
49 Posts |
 Posted - 2010-10-27 : 18:19:29
|
| I have a column that has christian names of customers and I want to get the initials ie John Harvey would be J H, Mary Jane Sarah would be M J S. Does anyone have any suggestions? I am fine to have only the first 2 initials if the name is more then 2 words so Mary Jane Sarah would be OK to be just M J |
|
|
Les.61
Starting Member
49 Posts |
Posted - 2010-10-27 : 18:47:59
|
I have located a way to do this so thought I would post to help others. This will cover up to 4 initials. If you need more just add extra lines but doing so in reverse ie line with 5 first then 4, 3, etc.  If you cut out lines then the initials do no work. I tried with just the lines with 1 & 2 and only got the 2nd & 3rd initial for a client with 3 names.  So suggest you go overboard and include more lines then necessary. I have gone to 6 lines.ISNULL(LEFT(PARSENAME(REPLACE(RTRIM(REPLACE(Client.GivenNames, ' ', ' ')), ' ', '.'), 4), 1) + ' ', '') + ISNULL(LEFT(PARSENAME(REPLACE(RTRIM(REPLACE(Client.GivenNames, ' ', ' ')), ' ', '.'), 3), 1) + ' ', '') + ISNULL(LEFT(PARSENAME(REPLACE(RTRIM(REPLACE(Client.GivenNames, ' ', ' ')), ' ', '.'), 2), 1) + ' ', '') + ISNULL(LEFT(PARSENAME(REPLACE(RTRIM(REPLACE(Client.GivenNames, ' ', ' ')), ' ', '.'), 1), 1) + ' ', '') AS Initials |
 |
|
|
TechNess
Starting Member
11 Posts |
Posted - 2010-10-27 : 19:39:37
|
Hello,This could be one way but if there is more than one space between first and last space, it will give you the first initial only.Declare @fullName varchar(100)set @fullName = 'Mary Jane Sarah'Select SUBSTRING(RTRIM(@fullName),1,1)+' '+SUBSTRING(@fullName,(charindex(' ',@fullName)),2)quote:
Originally posted by Les.61
I have a column that has christian names of customers and I want to get the initials ie John Harvey would be J H, Mary Jane Sarah would be M J S. Does anyone have any suggestions? I am fine to have only the first 2 initials if the name is more then 2 words so Mary Jane Sarah would be OK to be just M J
**TechNess** |
|
|
|
|
|
|
|
|
Creating Initials from Names