[Resolved] Using the "where" clause with "like"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Using the "where" clause with "like"

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-09-18 : 12:49:10

Have following query:

declare @JdeProduct varchar(10)
set @JdeProduct = 'D%'

DECLARE @SQLSTR NVARCHAR(4000) 
SET @SQLSTR = 
'SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,  ''Select rcpex1 from VGITFRLIB.F551202A where rcpex1 like (''' +  @JdeProduct + ''')'' '
print @SQLSTR  

EXEC (@SQLSTR)

Error:

SELECT * FROM OPENQUERY(AS400SRV_MSDASQL, 'Select rcpex1 from VGITFRLIB.F551202A where rcpex1 like replace('D%')'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D'.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-18 : 13:43:42

try this:


DECLARE @SQLSTR NVARCHAR(4000) 
SET @SQLSTR = 
'SELECT * FROM OPENQUERY(''AS400SRV_MSDASQL'',  ''Select rcpex1 from VGITFRLIB.F551202A where rcpex1 like ''''' +  @JdeProduct + ''''''' '
print @SQLSTR

Be One with the Optimizer
TG

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-09-18 : 14:32:38

Tried:

declare @JdeProduct varchar(10)
set @JdeProduct = 'D%'
DECLARE @SQLSTR NVARCHAR(4000) 
SET @SQLSTR = 
'SELECT * FROM OPENQUERY(''AS400SRV_MSDASQL'',  ''Select rcpex1 from VGITFRLIB.F551202A where rcpex1 like ''''' +  @JdeProduct + ''''''' '
print @SQLSTR  
EXEC (@SQLSTR)

Error:

SELECT * FROM OPENQUERY('AS400SRV_MSDASQL', 'Select rcpex1 from VGITFRLIB.F551202A where rcpex1 like ''D%'''
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'AS400SRV_MSDASQL'.

Here is a query that works using same table but without a variable

DECLARE @SQLSTR NVARCHAR(4000) 
SET @SQLSTR = 
'select * from openquery (AS400SRV_MSDASQL, ''select rcpex1  from  VGITFRLIB.F551202A where rcpex1 like (''''D%'''')'')'
print @SQLSTR  
EXEC (@SQLSTR)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-18 : 14:58:52

Ok, I added the single quotes around "AS400SRV_MSDASQL". Try removing that back to the way you had it.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-18 : 15:03:37

You can remove the EXEC statement and just compare the printed working version with the printed non-working, variable version and see that you were just missing some single quotes. I had also removed the () around the value because they are un-necessary. But it's fine to leave it in as well.

Be One with the Optimizer
TG

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-09-18 : 15:40:42

Ok, here is the solution and working code:

declare @JdeProduct varchar(10)
set @JdeProduct = 'D%'
DECLARE @SQLSTR NVARCHAR(4000) 
SET @SQLSTR = 
'SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,  ''Select rcpex1 from VGITFRLIB.F551202A where rcpex1 like (''''' +  @JdeProduct + ''''')'')'
print @SQLSTR  
EXEC (@SQLSTR)

Now I have one more request:

The variable @JdeProduct is passed in as (example) 'D*'. SO I need to replace the '*' with a '%' in my query string, something like this:

where rcpex1 like (''''' +  @JdeProduct + ''''',''''*'''',''''%'''')'')'

Cannot see to get this last piece going.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-09-18 : 15:43:48

Got last piece working:

declare @JdeProduct varchar(10)
set @JdeProduct = 'D*'
DECLARE @SQLSTR NVARCHAR(4000) 
SET @SQLSTR = 
'SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,  ''Select rcpex1 from VGITFRLIB.F551202A where rcpex1 like replace(''''' +  @JdeProduct + ''''',''''*'''',''''%'''')'')'
print @SQLSTR  
EXEC (@SQLSTR)

Thank you very much TG....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources