CODEWITHSUNDEEP
stringamazon-redshiftsql-like
gafeta
gafeta

Reputation: 23

Redshift LIKE column value with % in WHERE clause

I'm trying to make this to work:

with a as (select '1'::varchar(32) as x), 
     b as (select '10'::varchar(32) as x)
select *
from b 
left join a using(x)
where b.x like '%1%'
;

x    
---- 
10

1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms]

but what I need to use is: 

where b.x like '%' || a.x || '%'
;

x    
---- 

0 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms]

Any ideas?

I've put this question even here Redshift LIKE column value with % where the idea comes from.

Thanks

Upvotes: 1

Views: 1832

Answers (1)

Jon Scott
Jon Scott

Reputation: 4354

You can use strpos function to see whether a is within b

see https://docs.aws.amazon.com/redshift/latest/dg/r_STRPOS.html

where strpos(b,a) > 0

Upvotes: 2

Related Questions