• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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