How to return data which contains multiple words in SQL
- How-Tos FAQs
- December 17, 2018
We can return data that contains multiple words in SQL using below two ways.
- LIKE Operator
- CHARINDEX Function
If we need to return rows that contain all the words, we should use AND in WHERE condition, but if we need to return rows that contain any of the words, we should use OR.
Let us consider the below ‘employee’ table with employee and address.
CREATE TABLE employee (
name VARCHAR(50),
address VARCHAR(200)
);
INSERT INTO employee
SELECT 'Jack', '777 Brockton Avenue, Abington MA 2351' UNION ALL
SELECT 'Rose', '30 Memorial Drive, Avon MA 2322' UNION ALL
SELECT 'John', '250 Hartford Avenue, Bellingham CO 2019' UNION ALL
SELECT 'Dave', '700 Oak Street, Brockton MA 2301' UNION ALL
SELECT 'Jack', '66-4 Parkhurst Rd, Chelmsford CA 1824' UNION ALL
SELECT 'Greg', '591 Memorial Dr, Chicopee MA 1020' UNION ALL
SELECT 'Mick', '55 Brooksby Village Way, Danvers TX 1923';Using LIKE operator
-- Returns rows with contain any of the address information
SELECT *
FROM employee
WHERE address LIKE '%avenue%'
OR address LIKE '%Abington%'
OR address LIKE '%MA%'
-- Output
name address
-----------------------------------------------
Jack 777 Brockton Avenue, Abington MA 2351
Rose 30 Memorial Drive, Avon MA 2322
John 250 Hartford Avenue, Bellingham CO 2019
Dave 700 Oak Street, Brockton MA 2301
Greg 591 Memorial Dr, Chicopee MA 1020
-- Returns rows with contain all of the address information
SELECT *
FROM employee
WHERE address LIKE '%avenue%'
AND address LIKE '%Abington%'
AND address LIKE '%MA%'
-- Output
name address
----------------------------------------------
Jack 777 Brockton Avenue, Abington MA 2351Using CHARINDEX function
-- Returns rows with contain any of the address information
SELECT *
FROM employee
WHERE CHARINDEX('avenue', address) > 0
OR CHARINDEX('Abington', address) > 0
OR CHARINDEX('MA', address) > 0
-- Output
name address
--------------------------------------------------
Jack 777 Brockton Avenue, Abington MA 2351
Rose 30 Memorial Drive, Avon MA 2322
John 250 Hartford Avenue, Bellingham CO 2019
Dave 700 Oak Street, Brockton MA 2301
Greg 591 Memorial Dr, Chicopee MA 1020
-- Returns rows with contain all of the address information
SELECT *
FROM employee
WHERE CHARINDEX('avenue', address) > 0
AND CHARINDEX('Abington', address) > 0
AND CHARINDEX('MA', address) > 0
-- Output
name address
-----------------------------------------------
Jack 777 Brockton Avenue, Abington MA 2351Up Next:
