Avatar of rafaelrgl
 asked on

substring from field inside query

Hi, I how can i get the middle name of this string inside the query


So, inside my query i want to get the middle name of those fields, that are L for the first row and BOSS for the second. the first row have & that indicates there is another name there, so the middle name is L. And also get the first name of the second person if appears after the & character.

how I can do this query:
select substring(********* from table1

result should be:

middle_name_first_person        first_name_second_person
L                                                      PSY
Microsoft SQL ServerMySQL ServerSQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Russ Suter

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Russ Suter

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`

Open 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
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.