SHARE
TWEET
Untitled
a guest
Jun 4th, 2019
122
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
- Hi experts, I believe this tricky task might be interesting FOR you.
- WITH settings AS (
- SELECT 'key1' AS KEY, 'include' AS what_to_do UNION ALL
- SELECT 'key2' AS KEY, 'include' AS what_to_do UNION ALL
- SELECT 'key3' AS KEY, 'exclude' AS what_to_do
- ),
- DATA AS (
- SELECT 'value_with_key1_and_other_stuff' AS string_with_data UNION ALL
- SELECT 'value_with_key2_and_other_stuff' AS string_with_data UNION ALL
- SELECT 'value_with_key3_and_other_stuff' AS string_with_data UNION ALL
- SELECT 'value_with_key4_and_other_stuff' AS string_with_data UNION ALL
- SELECT 'value_with_key1_key3_and_other_stuff' AS string_with_data UNION ALL
- SELECT 'just_a_test' AS string_with_data
- )
- SELECT * FROM DATA
- I got two TABLES, DATA AND settings. Settings TABLE shows KEYS AND what TO do WITH them, include OR exclude. TABLE DATA has strings that contain different VALUES, SOME OF them include KEYS FROM settings, SOME NOT. IN fact, I'm looking for solution to combine below query in the end:
- data as (
- select 'value_with_key1_and_other_stuff' as string_with_data union all
- select 'value_with_key2_and_other_stuff' as string_with_data union all
- select 'value_with_key3_and_other_stuff' as string_with_data union all
- select 'value_with_key4_and_other_stuff' as string_with_data union all
- select 'value_with_key1_key3_and_other_stuff' as string_with_data union all
- select 'just_a_test' as string_with_data
- )
- select * from data
- where (upper(string_with_data) like upper('%key1%') or upper(string_with_data) like upper('%key2%')) and upper(string_with_data) not like upper('%key3%');
- But instead of hardcoding keys, I'd LIKE TO USE settings TABLE. How do I achieve that?
- Oh, AND my PostgreSQL version :
- PostgreSQL 10.8 (Ubuntu 10.8-0ubuntu0.18.04.1) ON x86_64-pc-linux-gnu, compiled BY gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.

