CODEWITHSUNDEEP
sqlsqlite
Cromon
Cromon

Reputation: 841

Select field on multiple values

I am currently trying to create a query where i want to select rows if one field contains multiple values. I think ill just give you an example:

Database: field="Ground;Lava;Rocks"

Query should be: select where field contains "Ground" and "Rocks".

My first attempt would have been:

SELECT * FROM TerrainLayer WHERE tags LIKE '%Ground%' AND tags LIKE '%Rocks%';

Would this be an acceptable solution or are the better (more efficient) ways to achieve this? Thank you in advance for your help.

Upvotes: 0

Views: 122

Answers (2)

sr28
sr28

Reputation: 5106

Something like this should also work, and if you have multiple LIKE clauses should be a little more efficient:

SQL

CREATE TABLE #SearchItem (Search varchar(255))

INSERT INTO #SearchItem VALUES
('Ground'),
('Rocks'),
('Whatever')

SELECT *
FROM TerrainLayer as t
JOIN #SearchItem as s
ON t.tags COLLATE DATABASE_DEFAULT LIKE '% ' + s.Search + ' %'

For SQLite

CREATE TABLE SearchItem (Search  varchar(255))

INSERT INTO SearchItem VALUES
('Ground'),
('Rocks'),
('Whatever')

SELECT *
FROM TerrainLayer as t
JOIN #SearchItem as s
ON t.tags LIKE '% ' + s.Search + ' %'

From what I tested a JOIN like this will be quicker. Obviously, you wouldn't need to manually populate the #SearchItem table like this.

Upvotes: 2

Siva
Siva

Reputation: 9101

try this:

SELECT * FROM TerrainLayer WHERE tags LIKE '%Ground%Rocks%';

Upvotes: 1

Related Questions