mySQL wildcards
- Started
- Last post
- 7 Responses
- acescence
i'm searching for a string in a db, but it's first name / last name in 2 different columns. i can easily do wildcard searches in first OR last, but can i search for the string in a concatenation of both columns? for example, if the string is 'john' or 'smith' i can match it, but what if it's 'john smith'??
- traffic_light0
SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable WHERE fullname like '%john%smith%'That's a start. The % is a wildcard in mysql.com
- acescence0
yeah, i tried that. the problem seems to be that you can't use the fullname field in the WHERE clause, only actual fields in the table.
thanks tho!
- cosmo0
why don't you concat(first_name, ' ', last_name) in the where clause and then try to match them? That should work.
- PonyBoy0
i love concatenation.
- acescence0
yes, thank you cosmo, that was what i needed to do.
- cosmo0
awesome.
- industry730
another option would be to create a index of the first and last name columns and do a search on that index
on one of my sites i have a fulltext index for firstname, lastname, email and username. so when my client needs to search for one of their users there is just 1 text field on the page and they can enter one of those strings to find the users info.