I face this error : Bad character in format or data of ipaddress.cdot
when I try to run the below code:
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
could someone help me identify the issue?
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?
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.