SQL Query to do a reverse CONTAINS search?

Question

I need to search for Article Tag strings that are sub-strings of a user entered string.

So in the below example, if a user searched for "normal", the query should return Article 1 and Article 3, as article 3 has a wildcard tag "norm*". If I searched for "normalization" then i should get back articles 3 and 4. Let me know if I need to explain my question more clearly.

Example-

• Article 1 Tag = normal
• Article 2 Tag = apple
• Article 3 Tag = norm*
• Article 4 Tag = normalization
• Article 5 Tag = corvette

Note - I only need to do the substring search on tags that end with an *

Answer 1

The easiest way to do it, but perhaps not the most efficient, is to replace all * by % in your table and use LIKE statment :

SELECT
  Tag
FROM
  Article
WHERE
  'normal' LIKE REPLACE(Tag, '*', '%')

See an example in SqlFiddle

Answer 2

I think this query should work, although I didn't test it beyond your sample data.

Also, you didn't specify what database you're using and I just tried it on MS SQL, but it should be easy to adapt to other databases as it only relies on charindex and left (or substring) and those functions should be available on most databases.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table your_table (article varchar(10), tag varchar (20))
insert your_table values 
('Article 1','normal'),
('Article 2','apple'),
('Article 3','norm*'),
('Article 4','normalization'),
('Article 5','corvette')

Query 1:

declare @str varchar(30) = 'normalization'

select t.article, tag 
from your_table t
left join (
    select 
       article, 
       left(tag, charindex('*', tag,0)-1) t, 
       charindex('*', tag,0)-1 as l 
    from your_table 
    where charindex('*', tag,0) > 0
    ) a
on t.article = a.article
where (tag = @str) or (left(@str, l) = t)

Results:

|   ARTICLE |           TAG |
|-----------|---------------|
| Article 3 |         norm* |
| Article 4 | normalization |

Query 2:

declare @str varchar(30) = 'normal'

select t.article, tag 
from your_table t
left join (
    select 
       article, 
       left(tag, charindex('*', tag,0)-1) t, 
       charindex('*', tag,0)-1 as l 
    from your_table 
    where charindex('*', tag,0) > 0
    ) a
on t.article = a.article
where (tag = @str) or (left(@str, l) = t)

Results:

|   ARTICLE |    TAG |
|-----------|--------|
| Article 1 | normal |
| Article 3 |  norm* |