Skip to Content
Aug 30, 2011 at 01:24 PM

How to replace EQUAL TO by LIKE in a Query involving variables


Dear All,

I am working on a Query which is in reality 3 Queries in 1. It works perfectly.

When I run the Query, it gives the following Query - Selection Criteria

Query - Selection Criteria
Import Log                        ...................
Block Number                      ...................

Document Date                     ...................
Document Date                     ...................

[OK]   [Cancel]

To use this Query, we must choose only 1 criterion and leave other fields blank. To achieve this, I

use OR inside WHERE

1ST Query

If you type S713 in the first field, it returns all records with Import Log = S713

2 ND Query

If you type G1575 in the 2ND field, it returns all records with Block Number = G1575

3rd Query

If you type '01.07.11' and '05.07.11' in the 3RD and 4TH fieldS, it returns all records with

Dates between 01.07.00 and 05.07.11

The Query won't work if you fill in all fields.


I want to replace the EQUAL TO in the WHERE clause by LIKE to allow the Query to search for a pattern, instead

of rendering an exact match.

I know this can be done with = '[%1]' by changing it to LIKE '%[%1]%'

But how can you do the same with @shipnum and @reqnum?

I can change the variable declarations to

set @shipnum = /* */ '%[%1]%'

but it creates problems.

How can we use LIKE in the WHERE clause, without modifying the declarations.


Leon Lai




Here's my Query

T0 = OPCH A/P Invoice - Header
T1 = PCH1 A/P Invoice - Rows
T5 = OJDT Journal Entry

declare @shipnum nvarchar (30)
set @shipnum =
/*select T1.[ImportLog] from [dbo].[PCH1] T1 where T1.[ImportLog]*/ '[%1]'

declare @reqnum nvarchar (30)
set @reqnum =
/*select T1.[BlockNum] from [dbo].[PCH1] T1 where T1.[BlockNum]*/ '[%2]'

declare @taxdt1 datetime
set @taxdt1=
/*select T0.[TaxDate] from [dbo].[OPCH] T0 where T0.[TaxDate]*/ '[%3]'

declare @taxdt2 datetime
set @taxdt2=
/*select T0.[TaxDate] from [dbo].[OPCH] T0 where T0.[TaxDate]*/ '[%4]'

T0.[TaxDate] AS 'Doc Dt',
T1.[ImportLog] AS 'Ship #',
T1.[BlockNum] AS 'Reqn #',
T0.[CardName] AS 'Supplier Name',
T0.[DocTotal] AS 'Rs'

FROM [dbo].[OPCH] T0
INNER JOIN [dbo].[PCH1] T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [dbo].[OJDT] T5 ON T0.[TransID] = T5.[TransID]


(T1.[ImportLog]  = @shipnum  AND 
@reqnum = '  ' AND
@taxdt1  = '  ' AND
@taxdt2  = '  ' )


(T1.[BlockNum] = @reqnum  AND 
@shipnum = '  ' AND
@taxdt1  = '  ' AND
@taxdt2  = '  ' )


(T0.[TaxDate] >= @taxdt1 AND
T0.[TaxDate] <= @taxdt2 AND
@shipnum = ' ' AND
@reqnum = ' ' )