CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Michael A
Michael A

Reputation: 9900

How to trim first name to a single character?

I have a column that has data stored in full name format, for example:

Tom Smith

Andrew Smith

Is there a function (or combination of functions) I can use to turn this into the following:

T. Smith

A. Smith

Upvotes: 0

Views: 174

Answers (3)

Niladri Biswas
Niladri Biswas

Reputation: 4171

Declare @t table(FullName Varchar(100))
Insert Into @t Values('Tom Smith'),('Andrew Smith'),('  Andrew '),('John Wayne Bobbitt'),(NULL)

--Solution

SELECT 
    NewName = CASE WHEN CHARINDEX(' ', LTRIM(RTRIM(FullName))) > 0 THEN LEFT(LTRIM(RTRIM(FullName)),1)
              + '.'
              +SUBSTRING((LTRIM(RTRIM(FullName))),CHARINDEX(' ', LTRIM(RTRIM(FullName))),LEN(FullName))
         ELSE LTRIM(RTRIM(FullName))
    END
FROM @t

//Result

NewName

T. Smith
A. Smith
Andrew
J. Wayne Bobbitt
NULL

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You should be storing first name and last name separately. In the meantime:

SELECT CASE WHEN FullName LIKE '% %' THEN 
  LEFT(FullName, 1) + '.' + SUBSTRING(FullName, CHARINDEX(' ', FullName), 255)
    ELSE FullName END
  FROM dbo.table;

Another alternative:

SELECT COALESCE(LEFT(FullName, 1) 
  + '.' + SUBSTRING(FullName, NULLIF(CHARINDEX(' ', FullName), 0), 255), 
  FullName)
FROM dbo.table;

Both of these deal with single-word names like Prince or Madonna - but they won't properly handle cases where you have middle names. The problem in that case will be:

John Wayne Bobbitt
Billy Ray Cyrus
Lisa Van der Wal
Lee Harvey Oswald
Dennis Moseley Williams
James de Caan

How do you determine which of those are middle names and which have multi-word last names? If you're dealing with this situation, start shaking your fists harder.

Upvotes: 5

Kermit
Kermit

Reputation: 34055

This should work:

SELECT SUBSTRING(nameCol, 1, 1) + '.' + SUBSTRING(nameCol, CHARINDEX(' ', nameCol), 100)

Upvotes: 0

Related Questions