regexp_replace woes

Database
Junior Contributor

Re: regexp_replace woes

First some control characters are replaced by a space:

\f = 0x0C form feed 

\n = 0x0A newline

\r = 0x0D carriage return

\t = 0x09 horizontal tab

\v = 0x0B vertical tab

And then remaining non-printable characters ([^[:print:]]) are removed.

Re: regexp_replace woes

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,

Teradata Employee

Re: regexp_replace woes

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.

Re: regexp_replace woes

using SUBSTR seemed to work. Thanks

Enthusiast

Re: regexp_replace woes

Hi,

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.

'', --replacestring
1, --startposition
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.

Highlighted
Teradata Employee

Re: regexp_replace woes

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.

Enthusiast

Re: regexp_replace woes

Thanks fred. This would be really helpful for me.