CODEWITHSUNDEEP
sqlpostgresqlamazon-web-servicesamazon-redshiftamazon-redshift-spectrum
KKP
KKP

Reputation: 77

Filter specific text in Redshift

I'm trying to filter the name of a table from a text column that contains SQL script in it. Sample below:

**columnA**        **ColumnB**
dt_1203       CREATE OR REPLACE VIEW public.demand_fcst_view AS
              SELECT id,region,snapshotday,demand
              FROM dtnet.dly_demand_fcst_all a

dt_1204       CREATE OR REPLACE VIEW public.demand_fcst_view AS
              SELECT id,region,snapshotday,demand
              FROM salesfrc.dly_demand_fcst_all

dt_1204       CREATE OR REPLACE VIEW public.demand_fcst_view AS
              SELECT id,region,snapshotday,demand
              FROM salesfrc.dly_demand_fcst_all_output

I only want rows where the text is "dly_demand_fcst_all" as my output, irrespective of which schema the table falls under or how many times the tables repeats.

I tried the query ColumnB like '%dly_demand_fcst_all' it is returning zero rows. I tried the query ColumnB like '%dly_demand_fcst_all%' it is returning all three rows

what am I missing here?

Upvotes: 0

Views: 517

Answers (1)

Lex Looter
Lex Looter

Reputation: 188

Probably you have some symbols at the end of the ColumnB. You can try using this filter, to filter only dly_demand_fcst_all table, and not dly_demand_fcst_all_

where column_b like '%dly_demand_fcst_all%'
and column_b not like '%dly_demand_fcst_all\\_%'

Or you can try TRIM function, for removing spaces

where trim(column_b) like '%dly_demand_fcst_all'

Upvotes: 2

Related Questions