Re: To get the FirstName and LastName from one word

Hello,

Please let me know how to retrieve data as a two names from one columns.

For example: My column has naming like this  Shannon.Cooley, I want to disply like this:

FirstName         LastName                           FullName

 Shannon        Cooley                            Shannon  Cooley

How to write the query to get the above table?

Thanks.

August 25th, 2015 4:43pm

If splitting based on the .

firstname = substring(fullname, 1, charindex('.', fullname)-1)

lastname = substring(fullname, charindex('.', fullname)+1, len(fullname))

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 4:52pm

Try using my splitter function

DECLARE @names TABLE (nameString NVARCHAR(100))
INSERT INTO @names (nameString) VALUES 
('Jean-Luc Picard'),('James Tiberious Kirk'),('William Thomas Riker'),('Rachel Garret'),('That Guy With Five Names')

;WITH singleNames AS (
SELECT *
  FROM @names
    CROSS APPLY dbo.splitter(nameString,' ')
), consNames AS (
SELECT *, (SELECT MIN(ID) FROM singleNames WHERE nameString = n.nameString) AS MIN, (SELECT MAX(ID) FROM singleNames WHERE nameString = n.nameString) AS MAX
  FROM singleNames n
 WHERE ID NOT IN ((SELECT MIN(ID) FROM singleNames WHERE nameString = n.nameString),(SELECT MAX(ID) FROM singleNames WHERE nameString = n.nameString))
), otherNames AS (
SELECT nameString, value, ID, min, max
  FROM consNames n
 WHERE ID - 1 = min
UNION ALL
SELECT a.nameString, a.value + ' ' + r.value, r.ID, a.min, a.max
  FROM otherNames a
   INNER JOIN consNames r
     ON a.nameString = r.nameString
	 AND a.ID + 1 = r.id
	 AND r.ID < a. max
)

SELECT f.value AS firstName, l.value AS lastName, o.value AS otherNames, f.value + COALESCE(' '+o.value,'')+' '+l.value AS fullName
  FROM @names n
    INNER JOIN singleNames f
	  ON n.nameString = f.nameString
      AND f.ID = (SELECT MIN(ID) FROM singleNames WHERE nameString = n.nameString)
    INNER JOIN singleNames l
	  ON n.nameString = l.nameString
      AND l.ID = (SELECT MAX(ID) FROM singleNames WHERE nameString = n.nameString)
	LEFT OUTER JOIN otherNames o
	  ON n.nameString = o.nameString
	  AND o.ID = (SELECT MAX(ID) FROM otherNames WHERE nameString = n.nameString)


August 25th, 2015 5:18pm

SELECT REPLACE('Shannon.Cooley', '.', SPACE(1)) AS [FullName]

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 5:41pm

Thanks for your reply. it works thank you.

Thanks.

August 25th, 2015 7:48pm

Thank you.
Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 7:50pm

Thank you.
August 25th, 2015 7:51pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics