• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Split initials from multiple names from one field

Hi team,

I'm struggling with SQL commands SUBSTRING and CHARINDEX to extract the initials of each name in the one field below

Adrian
Adrian John
Cornelia Maria Margaretha
Alison McMillan Julia Frances
Adrienne Alice Fowler Lorraine Joy
Joy Catherine Gertrude Maria Wihelmina Toni

What I need is the first letter of each name for addressing purposes.

All help is much appreciated.

Cheers
Bernard
0
BernardGBailey
Asked:
BernardGBailey
  • 5
  • 5
1 Solution
 
dbbishopCommented:
What do you mean the initials of each name?
From your examples and what you stated, from the above examples, the initials of each name means you would want:
A
AJ
CMM
AMJF
AAFLJ
JCGMWT
Is that what you are trying to get?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
DECLARE @Name VARCHAR(1000) ,@i INT, @Out VARCHAR(100)
SELECT @Name ='Joy Catherine Gertrude Maria Wihelmina Toni'
SET @i = CHARINDEX(' ',@Name )
SET @out = LEFT(@Name,  1)


WHILE CHARINDEX(' ', @Name, @i+1 )  <>0
BEGIN
      SELECT @i = CHARINDEX(' ', @Name, @i+1 )
      SET @Out  = @Out + SUBSTRING (@Name, @i+1, 1)

END

SELECT @Out
0
 
BernardGBaileyAuthor Commented:
Sorry,

I should have said I wanted them like

A
A J
C M M
A M J F
A A F L J
J C G M W T

Thanks for your interest.

Cheers
bernard
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
BernardGBaileyAuthor Commented:
Hi aneeshattingal,

I'm not sure how to run this as a sql query?
Cheers
Bernard
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Did you copy all the entire script , pasted it in Query analyser and try running ?  If you need to run against a table, we can convert this into a function

aneesh
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
here is the user defined function version of the above,


CREATE FUNCTION dbo.RetInitials (@Name  VARCHAR(1000) )
RETURNS VARCHAR(100)
AS BEGIN
      DECLARE @i INT, @Out VARCHAR(100)
      SET @i = CHARINDEX(' ',@Name )
      SET @out = LEFT(@Name,  1)
      
      
      WHILE CHARINDEX(' ', @Name, @i+1 )  <>0
      BEGIN
            SELECT @i = CHARINDEX(' ', @Name, @i+1 )
            SET @Out  = @Out + SUBSTRING (@Name, @i, 2)
      
      END

      RETURN(@Out)
END
GO
      

SELECT urColumnName, dbo.RetInitials (urColumnName)AS Initials
FROM urTableName
GROUP BY urColumnName
0
 
BernardGBaileyAuthor Commented:
Hi Aneesh,

the table name is initials

Cheers Bernard
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
u just need to replace 'urtablename' with 'initials' and 'UrColumnName' with the column name which stores these values
0
 
BernardGBaileyAuthor Commented:
Hi Anees,

 not entirely successful

Adrian       A
Adrian John      A
Adrienne Alice Fowler Lorraine Joy      A F L J
Alison McMillan Julia Frances      A J F
Cornelia Maria Margaretha      C M
Joy Catherine Gertrude Maria Wihelmina Toni      J G M W T


Missing initials from rows 2 to 6

 Looking good though
Cheers
bernard  
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Okay, my mistake
You need to set the value of  @i to zero  instead of "SET @i = CHARINDEX(' ',@Name )  ", remove that line and replace it with

" SET @i = 0 "

0
 
BernardGBaileyAuthor Commented:
Hi anees,

That works fine now.  The code I am now using:

CREATE FUNCTION dbo.RetInitials (@Name  VARCHAR(1000) )
RETURNS VARCHAR(100)
AS BEGIN
      DECLARE @i INT, @Out VARCHAR(100)
      SET @i = 0
      SET @out = LEFT(@Name,  1)
     
     
      WHILE CHARINDEX(' ', @Name, @i+1 )  <>0
      BEGIN
            SELECT @i = CHARINDEX(' ', @Name, @i+1 )
            SET @Out  = @Out + SUBSTRING (@Name, @i, 2)
     
      END

      RETURN(@Out)
END
GO
     
SELECT entitycode,
firstname, dbo.RetInitials (firstname)AS Initials
FROM encentral.dbo.tbl_Entity
GROUP BY  entitycode,firstname
 
I can now run this in a query to pickup the requisite information.

Many thanks

Cheers
Bernard
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now