I have a list of names that are either a company name or a person's name. I have to manually decide if it's a company or a person. I have looked at each row and used a case to decide. If it's a company a one is placed. If it is a person, a zero is placed. Also, I have to parse the individual names into columns of first and last. There are 20 lines for each whe statements. It seems that pervasive can't handle all the statements. Am I doing this wrong? Is there a simpler way?
Example
Own_name
Dollar Tree LLC
Jacob Smith
New Life Co
Johnson, Robert
Here's a sample of what the SQL looks like:
select
CASE
WHEN own_name LIKE '%co%'
OR own_name LIKE '%LLC%
THEN 1
END AS is_compan, // if the own_name has Co or LLC, place a 1 in a column
CASE
WHEN own_name NOT LIKE '%co%'
OR own_name NOT LIKE '%LLC%
THEN 0
END AS is_individual, //if the own_name DOES NOT have Co or LLC, place a 0 in a column
CASE
WHEN locate(',', own_name)>0
AND own_name NOT LIKE '%co%'
AND own_name NOT LIKE '%LLC%'
THEN ltrim(left(own_name, locate(',', own_name)-1))
WHEN locate(' ', own_name)>0
AND own_name NOT LIKE '%co%'
AND own_name NOT LIKE '%LLC%'
THEN SUBSTRING(own_name, locate(' ', own_name) + 1, 8000) //if the own_name has a comma or a space and does not have CO or LLC in the column, parse the name for the last name
END AS LAST_NAME
CASE
WHEN locate(',', own_name)>0
AND not locate('-', own_name)<0
AND own_name NOT LIKE '%co%'
AND own_name NOT LIKE '%LLC%'
THEN ltrim(left(own_name, locate(',', own_name)-1))
WHEN locate(',', own_name)=0
AND own_name NOT LIKE '%corp%'
AND own_name NOT LIKE '%LLC%'
THEN Ltrim(SubString(own_name,1,Isnull(Nullif(locate(' ',own_name),0),1000))) //if the own_name has a comma or a space and does not have CO or LLC in the column, parse the name for the first name
END AS First_name
The result
Own_name is_compan is individual Last name First name
Dollar Tree LLC 1
Jacob Smith 0 Smith Jacob
New Life Co 1
Johnson, Robert 0 Johnson Robert