Trimming value from a column in Snowflake-CodePudding

I have column called File with values "Mens_Purchaser_Segment_Report" and 'Loyalist_Audience_Segment_Report". I want to capture everything that comes before word Segment.

I used query: select TRIM(file,regexp_substr(file, '_Segment_Report.*')) as new_col

Output: Mens_Purch Loyalist_Audi

How do I capture everything before Segment?

Tried below but same results--> TRIM(file,regexp_substr(file, 'S.*'))

TRIM(file,regexp_substr(file, '_S.*'))

CodePudding user response：

You didn't specify if the trailing text is always _Segment_Report, you're asking for any text before _Segment. Depending on that various solutions can be used, see below.

create or replace table foo(s string) as select * from values 
  ('Mens_Purchaser_Segment_Report'),
  ('Loyalist_Audience_Segment_Report');
  
-- If you know the suffix you want to remove is always exactly '_Segment_Report'
select s, replace(s, '_Segment_Report', '') from foo;

-- If you know the suffix you want to remove starts with '_Segment' but can have something after
--   - approach 1, where we replace the _Segment and anything after it with nothing
select s, regexp_replace(s, '_Segment.*', '') from foo;

--   - approach 2, where we extract things before _Segment
--     Note: it will behave differently if there are many instances of '_Segment'
select s, regexp_substr(s, '(.*)_Segment.*', 1, 1, 'e') from foo;

CodePudding user response：

try

using regexp_replace

select regexp_replace(fld1, 'Segment', '') from (
select 'Mens_Purchaser_Segment_Report and Loyalist_Audience_Segment_Report' fld1 from dual );