Need help with wildcards in WHERE clause

[bigalligator]

I am trying to use a like comparison with wildcards and I can't seem to get anything to work. My query:

select USERID as email
from RADplus_users
where userrole like replace( right('&ACC&', len('&ACC&') - 1), '&', 'SUP ')

I want to put wildcards around the "replace( right('&ACC&', len('&ACC&') - 1), '&', 'SUP ')" but everything I try errors out. Where % would normally go on either side is not working for me. Can someone assist?

 

[bigalligator]

What I'm trying to do is find any record where it's like "%ACCSUP%

 

[simian336]

So...

select USERID as email
from RADplus_users
where userrole like '%ACCSUP%'

Does not produce results or errors out?

Simi

 

[bigalligator]

It does, but I need to use the formula in the query to remove the first character, and the replace the '&' that is after the ACC and replace it with 'SUP'
I just wanted to display what my point was with the query.

 

[bigalligator]

I put ACCSUP in there as a temporary literal string in place of a db field for this forum.

 

[bborissov]

SELECT  REPLACE(SUBSTRING('&ACC&',2,8000),'ACC&','ACCSUP%')

?

Borislav Borissov
VFP9 SP2, SQL Server

 

[bigalligator]

bborissov, that code did not work exactly, but it did make me realize that I could place the % inside the formula for the same outcome, so what I did is this:

where userrole like {fn concat( '%', replace( right('&ACC&', len('&ACC&') - 1), '&', 'SUP%')) }

Thank you.