The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Delete specific word using regexp_replace
I'm using below command to delete .co but it is not removing.
SELECT REGEXP_REPLACE('110 Companies .co' ,('^.co$'), '',1,0,'i')
Instead I tried SELECT REGEXP_REPLACE('110 Companies .co' ,'.co', '',1,0,'i') but ended up in wrong result.
I could not find much info about regexp_replace and it is really hard time for me.
Try using a backslash in your search string, as in '\.co'
And if you want to delete only the last occurrence of '.co' do:
If you want to be - at least slightly - more performant, try:
You are not loading all the REGEX environment, and "just" perform a string operation .....
What might be more useful here instead, is to just return the integer portion of that string - the 110. This function will do that.
CREATE OR REPLACE FUNCTION fn_GetOnlyNumeric (Source varchar(255))
-- use regex to take all the character fields in a string, leaving only numeric fields, and return that. \D says find all digits, and just return those. super simple.
RETURN REGEXP_REPLACE(Source, '\D') ;
ok, it seems for one particular pattern string I can use REGEXP_REPLACE, but here I need to operate on dynamic pattern
something like this; REGEXP_REPLACE(column1, '\'||column2 , '') but it throws error. Is there a limitation only to use pattern as string?
Where as for I'm able to do above operation using REPLACE(column1, column2 , '') but again REPLACE has limitations like case sensitivity, replace only for exact match etc. Obviously I can use LOWER() for comparing the strings but final output case is getting changed.
Get Well Soon --> Get
SELECT REPLACE('Get Well Soon', 'Well.' , '') -- This is not replacing and I need to trim 'Well Soon' by using 'Well'
REGEXP_REPLACE('Get Well Soon', 'Well.' , '') --> Get (But I want to use a column for second argument)
What is the best way to perform this operation where argument 1 and argument 2 are columns (dynamic values).
You can also try this:
You have the same string, and different parts to remove (or replace by an empty string):
Play with different values in the initial WITH clause to find out if you get the right behaviour ....
The solution is feasible for single pattern, but as I mentioned Requirement is for entire column which consists thousands of pattern which I need to match with one more column and remove the sub string.
The following example on one million rows takes 32 ms.
Does it address your need?
I think this should help, let me try and confirm you!