Asked  7 Months ago    Answers:  5   Viewed   25 times

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.

Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readability and flexibility of my SQL statements.

Is there any possible way to combine these two things without writing complicated sub-selects?

I want something as easy as WHERE something LIKE ('bla%', '%foo%', 'batz%') instead of this:

WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'

I'm working with SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.

 Answers

42

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

The column you are querying must be full-text indexed.

Reference:

  • Building Full-Text Search Applications with Oracle Text
  • Understanding SQL Server Full-Text
Tuesday, June 1, 2021
 
supermitch
answered 7 Months ago
67

There is no combined LIKE and IN syntax but you can use LIKE to JOIN onto your query as below.

;WITH Query(Result) As
(
SELECT '% Main Street' UNION ALL
SELECT 'foo %'
)
SELECT DISTINCT s.* 
FROM Street s
JOIN Query q ON StreetName LIKE q.Result

Or to use your example in the comments

SELECT DISTINCT s.* 
FROM Street s
JOIN CarStreets cs ON s.StreetName LIKE cs.name + '%'
WHERE cs.Streets = 'offroad'
Sunday, August 1, 2021
 
laconicdev
answered 5 Months ago
40

Ok, after a bit of hell, with the help of the stackoverflowers' answers I've found the solution that fits my needs.


SELECT
  SUM(CAST((DATE1 + 0) - (DATE2 + 0) AS FLOAT) AS SUM_TURNAROUND
FROM MY_BEAUTIFUL_TABLE
GROUP BY YOUR_CHOSEN_COLUMN

This returns a float (which is totally fine for me) that represents days both on Oracle ant SQL Server.

The reason I added zero to both DATEs is because in my case date columns on Oracle DB are of TIMESTAMP type and on SQL Server are of DATETIME type (which is obviously weird). So adding zero to TIMESTAMP on Oracle works just like casting to date and it does not have any effect on SQL Server DATETIME type.

Thank you guys! You were really helpful.

Monday, August 23, 2021
 
zerzer
answered 4 Months ago
94

No, there is no equivalent in T-SQL.

Monday, October 11, 2021
 
Webroots
answered 2 Months ago
12

You can also use combinations to generate unique combinations directly:

n = 8
n1 = 4
for x in itertools.combinations( xrange(n), n1 ) :
    print [ 1 if i in x else 0 for i in xrange(n) ] 

[1, 1, 1, 1, 0, 0, 0, 0]
[1, 1, 1, 0, 1, 0, 0, 0]
[1, 1, 1, 0, 0, 1, 0, 0]
[1, 1, 1, 0, 0, 0, 1, 0]
...
[0, 0, 0, 1, 1, 1, 0, 1]
[0, 0, 0, 1, 1, 0, 1, 1]
[0, 0, 0, 1, 0, 1, 1, 1]
[0, 0, 0, 0, 1, 1, 1, 1]

This is more efficient than permutations because you don't iterate over unwanted solutions.

The intuition is that you're trying to find all possible ways to fit four "1"s in a sequence of length 8; that's the exact definition of combinations. That number is C(8,4)=8! / (4! * 4!) = 70. In contrast, the solution using permutations iterates over 8! = 40,320 candidate solutions.

Sunday, November 21, 2021
 
Null
answered 2 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share