Check for special character in a string

Database
Fan

Re: Check for special character in a string

Hi Friends,

I face this error : Bad character in format or data of ipaddress.cdot

when I try to run the below code:

select

     ip_addr ,

   cast ( substr ( ip_addr , 1 , adot - 1 ) as bigint ) * 16777216 +

   cast ( substr ( ip_addr , adot + 1 , bdot - 1 - adot ) as integer ) * 65536 +

   cast ( substr ( ip_addr , bdot + 1 , cdot - 1 - bdot ) as integer ) * 256 +

   cast ( substr ( ip_addr , cdot + 1 , ddot - 1 - cdot ) as integer ) as ip_integer ,

   cast ( ip_integer / 16777216 as bigint ) as ablock ,

   cast (( ip_integer - ablock * 16777216 ) / 65536 as bigint ) as bblock

   from ipaddress5

could someone help me identify the issue?

Thanks,

GIRINJ

Enthusiast

Re: Check for special character in a string

How do i remove special characters and letters. I'm using below query to remove this but it is throwing an error saying 'The string contains an untranslatable character'.

SyntaxEditor Code Snippet

sel
     TRIM(REGEXP_REPLACE(REGEXP_REPLACE(CAST(LAST_NM AS CHAR(3000)), '[\t\r\n\v\f|]',' ',1,0, 'i'), '[^[:print:]]','',1,0, 'i'))
     from DATA_CORE.ASSC

sample data: Jed Iñigo, Ordoñez.

 

can anyone please suggest on this? 

Teradata Employee

Re: Check for special character in a string

It appears your data is UTF-8 encoded but stored in a LATIN field? So actually fixing the data and loading it properly might be the first order of business.

But the error occurs on the attempt to translate LAST_NM to UNICODE (it must have some x'1A' characters) to match the other arguments (string literals are always UNICODE).

Use CHR function to generate the values instead of string literals, or explicitly translate all the literals, e.g. TRANSLATE('i' USING UNICODE_TO_LATIN).

 

And if you want to continue this discussion, please start a new topic.

Enthusiast

Re: Check for special character in a string

Thanks Fred, i have used LATIN_TO_UNICODE it worked for me.

Highlighted
Enthusiast

Re: Check for special character in a string

We can use the regular expression like :

 

WHERE

CHARACTERS(TRIM(
REGEXP_REPLACE(COL_NAME,'[A-Z,0-9]*','',1,0,'i')
))>0;

DARORA