[SQL] Partial search in a field
Solved
sly-bzh
Posted messages
417
Status
Member
-
Fred -
Fred -
Hello everyone,
I just started learning SQL and after some research, I've encountered a problem that I can't seem to solve.
Let me give you an example that may not be very relevant but will help clarify my issue:
in one of my tables, I have a field called NAME that contains people's names in the format LASTNAME_FIRSTNAME. I would like to know if it is possible to run an SQL query that selects all people with the same LASTNAME regardless of the FIRSTNAME (a kind of WHERE condition 'contains at least LASTNAME')
I know that my example isn't great since it's very easy to create two tables, one for last names and the other for first names and link them, but I'm in a situation where I can't do that; I have data in the format XXX_YYY and I absolutely need to retrieve all the XXX. I didn't create it, and the database has several thousand entries, so there's no way I can rewrite everything.
Thank you in advance.
I just started learning SQL and after some research, I've encountered a problem that I can't seem to solve.
Let me give you an example that may not be very relevant but will help clarify my issue:
in one of my tables, I have a field called NAME that contains people's names in the format LASTNAME_FIRSTNAME. I would like to know if it is possible to run an SQL query that selects all people with the same LASTNAME regardless of the FIRSTNAME (a kind of WHERE condition 'contains at least LASTNAME')
I know that my example isn't great since it's very easy to create two tables, one for last names and the other for first names and link them, but I'm in a situation where I can't do that; I have data in the format XXX_YYY and I absolutely need to retrieve all the XXX. I didn't create it, and the database has several thousand entries, so there's no way I can rewrite everything.
Thank you in advance.
Related links:
- SQL: copy a table into another...
- [SQL] Find the maximum between 2 fields
- Le rôle exact du (+) dans une clause est de spécifier une jointure externe à gauche (left outer join) dans une requête SQL.
- SQL: Rounding in the SELECT
- [SQL] Duplicate column + values in table
- insérer un commentaire dans une requête SQL Access
7 answers
-
Why not put the first and last names in separate columns?
You could do WHERE LASTNAME like "%pierre%" -
In fact, the table is not called NAME and the contents are not NOM_PRENOM
These are very complex technical terms and I simplified them so that it's easier to understand, but since the structure is XXXX_YYYYY, it comes down to the same thing.
Initially, the 2 fields were not created because at the time there was no usefulness in doing so, but I am currently completely overhauling the entire database and today it is becoming almost mandatory to do it.
I will try WHERE like "%....%" and as soon as I have the answer, I will come back. -
It works, thank you very much :D
PS. I just noticed that the title of the discussion is only [SQL] I don't know why it didn't take my whole title, I'm sorry about that but I can't change it. -
Sure.
For the title, tell me what you want to put, and I'll correct it. -
Well, initially I had put [SQL]"arguments" of WHERE but I don't know if we can really call it an argument here.
It's up to you, if this title works for you, you can choose it or I can leave it to your imagination. -
I am looking for certain people from November 2008 to February 2009.
-
One solution is to save your table in a CSV file format. Then you open the CSV file again in Access (import), but in the import wizard, you define a column with the "_" sign. Access will then import 2 columns for you: one for first names and the other for last names.
