How to select with N like

Question

I have a problem with my project when work with MSSQL

TableName: tbA

ID | COL
---+------
1  | 'abc'
2  | 'azc'
3  | 'xyz'
4  | '123'

Proceduce: pSearch(@input string)

I want to select tbA combines LIKE, IN and OR with ideal:

SELECT * FROM tbA a WHERE a.Z IN ('"a%c" OR "x%"')

Something like it, and maybe I have a solution that's full-text search but I don't want to use it.

And my expectation

ID | COL
---+------
1  | 'abc'
2  | 'azc'
3  | 'xyz'

I hope people give me a solution or something else, thanks all.

Similar Question Here [http://stackoverflow.com/questions/16459144/shorten-sql-syntax-of-like-clause-to-in-clause](http://stackoverflow.com/questions/16459144/shorten-sql-syntax-of-like-clause-to-in-clause) — Romesh, Aug 06 '13 at 10:09

score 4 · Answer 1 · edited Aug 06 '13 at 09:42

4

Variant 1.

SELECT * FROM tbA a WHERE a.Z LIKE 'a%c' OR a.Z LIKE 'x%'

Variant 2.

DECLARE @tPattern_ TABLE (pat VARCHAR(50));

INSERT INTO @tPattern_ (pat)
SELECT 'a%c' UNION ALL SELECT 'x%'

SELECT DISTINCT a.*
FROM tbA a
INNER JOIN @tPattern_ p ON a.Z LIKE p.pat

edited Aug 06 '13 at 09:42

Igor Borisenko

3,806
3
34
49

answered Aug 06 '13 at 09:38

GriGrim

2,891
1
19
33

score 1 · Answer 2 · answered Aug 06 '13 at 09:59

1

Variant 3.

DECLARE @temp TABLE (title NVARCHAR(50))
INSERT INTO @temp (title)
VALUES ('a c'), ('x1111'), ('blank')

SELECT t.*
FROM @temp t
WHERE EXISTS(
    SELECT 1
    FROM (VALUES ('a%c'), ('x%')) c(t)  
    WHERE title LIKE t
)

answered Aug 06 '13 at 09:59

Devart

119,203
23
166
186

How to select with N like

2 Answers2