substring from field inside query

This is an age-old problem. The issue is that you have no normalized data. How do you parse a name like John Jacob Jingleheimer Schmidt? Is his middle name "Jacob", or "Jingleheimer ", or "Jacob Jingleheimer"? And what do you do if the name is entered as "Schmidt, John Jacob Jingleheimer"? How do you differentiate that?

In general, SQL isn't very good at string manipulation. That's not what it's designed for. You should move such logic into the business layer of your application.

All that being said, if you set up some parameters it is possible to retrieve the data you're looking for... most of the time. So, how would you address the above situation? What are your parsing rules?

Russ, the data is normalize, the last name and first name is separated by comma at the beggining of the string than we have the second name and if we get another name will come after the & character and the first word after that will always be the first name of the second person.

the example you sad above does not happen inside this data since is normalize.  this data will come like this:

(last_name_1person),(first_name_1person) (second_name_if_there_is_any_1_person) & (first_name_2person) (second_name_2_person) (last_name_2_person).

Some times we have the second person, but we will always have the 1person first name and last name, only the second name that we can have it or not.

OK, I see what you're getting at. You added some data to your original post that helps clarify. But be clear, this is NOT normalized data. You may be imposing some rules on how the data are entered (either by software or by convention) but the database is not normalized. You just happen to have a pattern you can work with.

Is this data all contained in a single column in a single row? Do you have multiple names in a single data cell? If the answer to those is yes then you really need to use something like regular expressions to accomplish this task. Microsoft SQL Server doesn't have built-in support for regular expressions. I don't know about MySQL. Again, you should consider moving this logic into the business layer of your application where regular expressions could be fully leveraged.

yes, this data is inside one field varchar(40). Yes there is only this patter like I show you before, maybe one name and sometimes 2 names with the & character separating them.

btw, to get the first and last name I just did this, my problem is to get the second name when there is one and get the first name of the second person when there is one.

    
   SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',-1),' ',1)) as `first_name_1person`,
   SUBSTRING_INDEX(name,',',1)) as `last_name_1person`

Select allOpen in new window

pls have a better look to my problem. I know we can use locate function and substring, but just can not put this together to get the other parameters