Q:

Trouble extracting First and LastName from Full Name column

I am having FullName column and I am extracting the First Name and last name using the following query 
select  SUBSTRING(FULL_NAME, 1, CHARINDEX(' ', FULL_NAME) - 1) AS FirstName,
        SUBSTRING(FULL_NAME, CHARINDEX(' ', FULL_NAME) + 1, 500) AS LastName
from [dbo].[TABLE]

But in the Full Name column there are just First names, some 10 digit phone numbers, 4 digit extensions and some text like 'this is a special case'.
How should I modify my query to accommodate these exceptions? And also when there are only single words in the Full Name column I am getting this following error message:

"Invalid length parameter passed to the LEFT or SUBSTRING function."

A:

Parsing good names from free form fields is not an easy task...
I would suggest a dual approach.
Identify common patterns, i.e.   you might find phone number with something like this
Where IsNumeric( Replace(Field,'-','')=1

and you might identify single names with
Where charindex(' ',trim(field))=0

etc.
Once you've identified them, the write code to attempt to split them...
So you might use the code you have above with the following WHERE clause
  select SUBSTRING(FULL_NAME, 1, CHARINDEX(' ', FULL_NAME) - 1) AS FirstName,
         SUBSTRING(PRQ_BP_CONTACT_NAME, CHARINDEX(' ', FULL_NAME) + 1, 500) 
         AS LastN
  from [dbo].[TABLE]
  where charindex(' ',trim(field))>0 and Where IsNumeric( Replace(Field,'-','')=0

Use the WHERE clauses to (a) make sure you only get records you can parse and (b) help identify the oddball cases you'll like need to do by hand...
Good luck

