Parsing through a column to flip names using patindex

Question

So I have a database of customers. I run SELECT * FROM MyTable it gives me back several columns, one of which is the name. Looks like this:

"Doe, John"
"Jones, Bill"
"Smith, Mike"
"Johnson, Bob"
"Harry Smith"
"Black, Linda"
"White, Laura"

etc. Some are last name, first name. Others are first name last name.

My boss wants me to flip the names so they are all first then last.

So I ran this:

SELECT SUBSTRING(Column_Name, CHARINDEX(',', Column_Name) + 2, LEN(Name) - CHARINDEX(',',  Column_Name) + 1) + ' ' + SUBSTRING(Column_Name, 1, CHARINDEX(',', Column_Name) - 1) FROM MyTable

The problem is that when I run that, it only runs the names until it finds one it doesn't need to flip. So in the example above, it would only give me the first four names, not all of them.

It was suggested to me that I could use the PATINDEX() to pull out all of the names. I don't know how to use this and was hoping I could get some help with it.

Does your code have TRY/CATCH? If so you're getting the first four rows and then missing that this raises an error. But first, why are you just swapping the first and last name and keeping them in the same column? These are different pieces of data. You should go back to your boss and suggest that instead of a Name column, you should have FirstName and LastName columns. The solution for that will be very similar to the update you're already trying to perform. — Aaron Bertrand, Oct 23 '13 at 18:52
No, there isn't a TRY/CATCH with this. Just the SELECT statement. As to why they are in the same column, I don't know, but it's the way it has to stay unfortunately. — Dylan James Creighton, Oct 23 '13 at 18:58
Well, you're getting an error, so if you're not using TRY/CATCH then you are using some other mechanism that ignores the error, or you're just not seeing it. — Aaron Bertrand, Oct 23 '13 at 19:00

score 1 · Accepted Answer · answered Oct 23 '13 at 18:57

I suspect your code has TRY/CATCH or you are otherwise swallowing/suppressing/ignoring errors. You should get 4 rows back and then a big ugly error message:

Msg 537, Level 16, State 2
Invalid length parameter passed to the LEFT or SUBSTRING function.

The problem is that your expression assumes that , always exists. You need to cater for that either by filtering out the rows that don't contain a , (though this is not very dependable, since the expression could be attempted before the filter), or the following way, where you make different decisions about how to reassemble the string based on whether a , is found or not:

DECLARE @x TABLE(y VARCHAR(255));

INSERT @x VALUES
('Doe, John'),
('Jones, Bill'),
('Smith, Mike'),
('Johnson, Bob'),
('Harry Smith'),
('Black, Linda'),
('White, Laura');

SELECT LTRIM(SUBSTRING(y, COALESCE(NULLIF(CHARINDEX(',',y)+2,2),1),255))
 + RTRIM(' ' + LEFT(y, COALESCE(NULLIF(CHARINDEX(',' ,y)-1,-1),0)))
FROM @x;

Results:

John Doe
Bill Jones
Mike Smith
Bob Johnson
Harry Smith
Linda Black
Laura White

score 0 · Answer 2 · answered Oct 23 '13 at 18:59

You don't need PATINDEX in this case, although it could be used. I'd take your expression to flip the names and put it in a CASE expression.

DECLARE @MyTable TABLE
(
   Name VARCHAR(64) NOT NULL
);

INSERT @MyTable(Name)
VALUES
   ('Doe, John'),
   ('Jones, Bill'),
   ('Smith, Mike'),
   ('Johnson, Bob'),
   ('Harry Smith'),
   ('Black, Linda'),
   ('White, Laura');

SELECT
   CASE
      WHEN CHARINDEX(',', Name, 1) = 0 THEN Name
      ELSE SUBSTRING(Name, CHARINDEX(',', Name) + 2, LEN(Name) - CHARINDEX(',',  Name) + 1)
         + ' ' + SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1)
   END AS [Name]
FROM @MyTable;

The first condition simply returns the original value if no comma was used.

Parsing through a column to flip names using patindex

2 Answers2