Written by

Sr Software Developer at Coast Professional

Question Andrew McCrevan · Jun 5, 2025

SQL Procedure - Equivalent to "IN LIKE"

#Caché #InterSystems IRIS #SQL

How would you go about creating an SQL Stored Procedure that would result in the same output as "IN LIKE"?
For example...
CustomersTable

RecordId	CustomerName
123	Mark Stevens
456	Betty Johnson
789	John Stevens
321	Brian Smith
654	John Markson
987	Tom Obrian

select *
from CustomersTable
where inLike('%Mark%', '%John%')

Would return:

123	Mark Stevens
456	Betty Johnson
789	John Stevens
654	John Markson

Product version: Caché 2017.1

$ZV: Cache for Windows (x86-64) 2017.1.3 (Build 319_6_20939) Fri Jun 4 2021 13:57:02 EDT

Discussion (5)2

Add reply

Comments

Ashok Kumar T · Jun 5, 2025

Here is the simplified version of the LIKE operator with SQL Procedure

Stored procedure

/// add multiple parameters depends on needs
Query NAMEINLIKE(p1 As %String = "", p2 As %String = "") As %Library.SQLQuery [ SqlProc ]
{
    SELECT  Name,Age FROM Sample.Person 
    WHERE Name like :p1 or Name like :p2
}

SQL query

select * FROM SAMPLE.PERSON_NAMEINLIKE('%Eisenstien%','Xenia%')

ClassMethod INLIKE(pSQLColumnValue, pSearchValues...) As %Boolean [ SqlProc ]
{
    Set rtn=0
    For i=1:1:$O(pSearchValues(""),-1) {
        Set data = pSearchValues(i)
        If $E(data,1)="%"&&($E(data,*)="%") {
            Set data = $TR(data,"%")
            If pSQLColumnValue[data s rtn=1
        }
        ElseIf $E(data)="%" {
            Set data = $TR(data,"%")
            Set pat = ".ANPC1"""_data_""".ANPC"
            If pSQLColumnValue?@pat Set rtn = 1
        }
        ElseIf $E(data,*)="%" {
            Set data = $TR(data,"%")
            Set pat = "1"""_data_""".ANPC"
            If pSQLColumnValue?@pat Set rtn = 1
        } 
    }
    quit rtn
}

SQL Query

SELECT * FROM SAMPLE.PERSON
WHERE 1=SAMPLE.PERSON_INLIKE(FirstName,'%vid','Zelda%')

0 0

Vitaliy Serdtsev · Jun 6, 2025

CREATE FUNCTION inLike(str VARCHAR(50), lstLike VARCHAR(50))
RETURNS BIT
PROCEDURE
LANGUAGE OBJECTSCRIPT
{
  set res = 0, ptr = 0
  while $listnext(lstLike,ptr,v) {
    &sql(select case when :str like :v then 1 else 0 end into :like)
    if like {
      set res = 1
      quit
    }
  }  
  quit res
}

Usage:

select * from CustomersTable where inLike(CustomerName,$LISTBUILD('%Mark%','%John%','%an%'))=1

0 0

Andrew McCrevan Jun 9, 2025 to Vitaliy Serdtsev

Thank you
I tried this and it worked

0 0

Joel Solon · Jun 6, 2025

I'd use LIKE and OR directly in the query. If there are more than 2-3 comparisons (which would make something like InLike() useful), I'd write a class method that uses Dynamic SQL to add an OR for each comparison to the final WHERE clause.

0 0

Andrew McCrevan Jun 9, 2025 to Joel Solon

Thanks Joel
Very simple & effective 👍

0 0