CODEWITHSUNDEEP
sqlsql-serverdatabaseazure-sql-database
Hemant Bavle
Hemant Bavle

Reputation: 3327

How to search a full name in sql server database table with only firstname and lastname column?

We have a DB table, User with first_name, last_name columns but do not have a full_name column. We want to perform a search on the table, where the search query can contain the string which is a combination of both columns or one of them or simply a character. Search has to case insensitive.

Edit 1 The search needs to be fast enough as the request would be from a typeahead on a webclient.

Upvotes: 0

Views: 12355

Answers (4)

Rajat
Rajat

Reputation: 5803

The LIKE expressions by default are case insensitive. As @larnu suggested, adding a persistent column is a better way of going about it. And if you can do a prefix search on that, it will further speed things up. If those are not an option, try this to see if you can tolerate the performance:

SELECT CONCAT_WS (" ", first_name, last_name) full_name
FROM YourTable
WHERE CONCAT_WS (" ", first_name, last_name) LIKE '%some string%';

Upvotes: 1

Leon Yue
Leon Yue

Reputation: 16411

You can use this query: 

SELECT first_name,last_name,  CONCAT(CONCAT(first_name,' '),last_name)  full_name  
FROM User_table WHERE CONCAT(CONCAT(first_name,' '),last_name) LIKE '%string%'

Or:

SELECT first_name,last_name,  CONCAT(first_name,last_name)  full_name  
FROM User_table WHERE CONCAT(fistName,lastName) LIKE '%string%'

For example, I create a table like you, and run this query get the result you want.

TB3:

enter image description here

Restult1:

enter image description here

Restult2:

enter image description here

Hope this helps.

Upvotes: 0

Matt
Matt

Reputation: 2869

If you're looking at the format of your search term being the same with a firstname[space]lastname combination, you could do something like the below:

SELECT *
FROM Names
WHERE LEFT([Firstname] + ' ' + [lastname], LEN(@searchname)) = @searchname

This would allow you to search by the length of your search name. An example of how this could work is below:

CREATE TABLE Names
(
    firstname NVARCHAR(100),
    lastname NVARCHAR(100)
)

INSERT INTO Names VALUES
('John', 'Smith'), ('Jane', 'Doe'), ('Harry', 'Potter')


DECLARE @searchname NVARCHAR(100)

--John (Fist Name match only)
SET @searchname = 'John'
SELECT *
FROM Names
WHERE LEFT([Firstname] + ' ' + [lastname], LEN(@searchname)) = @searchname

--Jane D (First name + Initial Last name)
SET @searchname = 'Jane D'
SELECT *
FROM Names
WHERE LEFT([Firstname] + ' ' + [lastname], LEN(@searchname)) = @searchname


--H (Single character only in correct order)
SET @searchname = 'H'
SELECT *
FROM Names
WHERE LEFT([Firstname] + ' ' + [lastname], LEN(@searchname)) = @searchname

Upvotes: 0

Guy Louzon
Guy Louzon

Reputation: 1203

A db table and an example would be easier to work with, but the simple case, should be something like the query below I would make sure the search string is converted to lower case. Notice that such a query on a large scale database might not be that quick

SELECT
*
FROM
users u
WHERE
 LOWER(CONCAT(u.first_name,u.last_name)) LIKE '%{@search_string_here}%'

Upvotes: 0

Related Questions