New post
Question
· Jun 5

SQL Procedure - Equivalent to "IN LIKE"

#SQL #Caché #InterSystems IRIS

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
Log in or sign up to continue
Ashok Kumar T · Jun 5

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%')
1 0
Vitaliy Serdtsev · Jun 6 
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
1 0