Is there a way to use LIKE and IN together?
I want to achieve something like this.
SELECT * FROM tablename WHERE column IN ('M510%', 'M615%', 'M515%', 'M612%');
So basically I want to be able to match the column with a bunch of different strings. Is there another way to do this with one query or will I have to loop over the array of strings I am looking for?
This question is related to
mysql
How about using a substring with IN.
select * from tablename where substring(column,1,4) IN ('M510','M615','M515','M612')
u can even try this
Function
CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20))
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
Query
select * from my_table inner join (select value from fn_split('M510', 'M615', 'M515', 'M612',','))
as split_table on my_table.column_name like '%'+split_table.value+'%';
substr([column name],
[desired starting position (numeric)],
[# characters to include (numeric)]) in ([complete as usual])
Example
substr([column name],1,4) in ('M510','M615', 'M515', 'M612')
I tried another way
Say the table has values
1 M510
2 M615
3 M515
4 M612
5 M510MM
6 M615NN
7 M515OO
8 M612PP
9 A
10 B
11 C
12 D
Here cols 1 to 8 are valid while the rest of them are invalid
SELECT COL_VAL
FROM SO_LIKE_TABLE SLT
WHERE (SELECT DECODE(SUM(CASE
WHEN INSTR(SLT.COL_VAL, COLUMN_VALUE) > 0 THEN
1
ELSE
0
END),
0,
'FALSE',
'TRUE')
FROM TABLE(SYS.DBMS_DEBUG_VC2COLl('M510', 'M615', 'M515', 'M612'))) =
'TRUE'
What I have done is using the INSTR function, I have tried to find is the value in table matches with any of the values as input. In case it does, it will return it's index, i.e. greater than ZERO. In case the table's value does not match with any of the input, then it will return ZERO. This index I have added up, to indicate successful match.
It seems to be working.
Hope it helps.
Use the longer version of IN which is a bunch of OR.
SELECT * FROM tablename
WHERE column LIKE 'M510%'
OR column LIKE 'M615%'
OR column LIKE 'M515%'
OR column LIKE 'M612%';
You can use a sub-query with wildcards:
SELECT 'Valid Expression'
WHERE 'Source Column' LIKE (SELECT '%Column' --FROM TABLE)
Or you can use a single string:
SELECT 'Valid Expression'
WHERE 'Source Column' LIKE ('%Source%' + '%Column%')
SELECT * FROM tablename
WHERE column IN
(select column from tablename
where column like 'M510%'
or column like 'M615%'
OR column like 'M515%'
or column like'M612%'
)
You'll need to use multiple LIKE
terms, joined by OR
.
For a perfectly dynamic solution, this is achievable by combining a cursor and a temp table. With this solution you do not need to know the starting position nor the length, and it is expandable without having to add any OR's to your SQL query.
For this example, let's say you want to select the ID, Details & creation date from a table where a certain list of text is inside 'Details'.
First create a table FilterTable with the search strings in a column called Search.
As the question starter requested:
insert into [DATABASE].dbo.FilterTable
select 'M510' union
select 'M615' union
select 'M515' union
select 'M612'
Then you can filter your data as following:
DECLARE @DATA NVARCHAR(MAX)
CREATE TABLE #Result (ID uniqueIdentifier, Details nvarchar(MAX), Created datetime)
DECLARE DataCursor CURSOR local forward_only FOR
SELECT '%' + Search + '%'
FROM [DATABASE].dbo.FilterTable
OPEN DataCursor
FETCH NEXT FROM DataCursor INTO @DATA
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #Result
select ID, Details, Created
from [DATABASE].dbo.Table (nolock)
where Details like @DATA
FETCH NEXT FROM DataCursor INTO @DATA
END
CLOSE DataCursor
DEALLOCATE DataCursor
select * from #Result
drop table #Result
Hope this helped
Source: Stackoverflow.com