Improve company productivity with a Business Account.Sign Up
Declare @str varchar(100) = 'Doe, John a'
SELECT LEFT(@str, CHARINDEX (',', @str, 1) - 1) as last_name,
RIGHT(@str, LEN(@str) - CHARINDEX (',', @str, 1) - 1 ) as first_name
Open in new window
CREATE TABLE MyTable
INSERT INTO MyTable
('Doe, John a')
cross apply (select CHARINDEX (',', debtorname + ',', 1), len(debtorname) ) as ca1 (pos1, len1)
cross apply (select
LEFT(debtorname, pos1 - 1)
, case when pos1 > len1 then null
else RIGHT(debtorname, len1 - (pos1 + 1))
) as ca2 (last_name, first_name)
| LAST_NAME | FIRST_NAME | DEBTORNAME |
| Flintstone | Fred | Flintstone, Fred |
| PortletPaul | (null) | PortletPaul |
| Doe | John a | Doe, John a |
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.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!