First some control characters are replaced by a space:
And then remaining non-printable characters ([^[:print:]]) are removed.
I'm getting a 3706 error in the following line saying I need something between pfp.plan_other_info and ','. This worked before but not now. My associate said he had the same issue. Can you help?
CAST((CASE WHEN pfp.plan_other_info LIKE ALL ('%evoucher%','%paid%') THEN REGEXP_SUBSTR(SUBSTRING(pfp.plan_other_info ,INDEX(pfp.plan_other_info ,'paid') ,INDEX(pfp.plan_other_info ,'toward') ) , '[0.00-9.00]+') +3 ELSE 0 END) AS DECIMAL(18,2)) AS evoucher_plan_ar,
SUBSTRING function requires FROM and optionally FOR keywords inside the parentheses; to use commas instead, change to SUBSTR function.
But note that the third argument is a length and not the ending position, i.e. you may want to use the difference of the INDEX values +1.
What is the use for this statement ? What kind of special characters it will remove?
TRIM(REGEXP_REPLACE(REGEXP_REPLACE(CAST(INOUT_STRING AS CHAR(3000)), '[\t\r\n\v\f|]',' ',1,0, 'I'), '[^[:print:]]','',1,0, 'I'));
SyntaxEditor Code Snippet
REGEXP_REPLACE(TRIM(Upper(TRIM(REGEXP_REPLACE(TRANSLATE(CAST(INOUT_STRING AS CHAR(3000)) USING LATIN_TO_UNICODE WITH ERROR) , '[\t\r\n\v\f|]',' ',1,0,'i'))) ), '[^[:print:]]', '',1,0,'i')
Here what is the exact meaning and use for below statement?
'[\t\r\n\v\f|]',' ',1,0,'i')))), '[^[:print:]]', '',1,0,'i')
I know the menaing for below statement. But, i want to know use for other statement as well.
0, -- occurrence, 0 = all
'i' -- match -> case insensitive.
Here is the sample data: ZORAIDA PEÑA
after applying this statement below is the result.
the output is: ZORAIDA PEA. It is removing Ñ this letter. is it valid? It is treating the complete letter as special characters? Can anyone please provide the list of special characters?
Please help me on this.
POSIX character classes such as [:print:] will only match ASCII characters (i.e. <= U+007F). So the negation [^[:print:]] matches all non-ASCII characters plus the non-printing ASCII control characters.