Bad character in format or data

Analytics

Bad character in format or data

Hi,

I have two tables, say Table1 and Table 2. Both the tables have the same column, Say 'Phone number'.
But the Datatype of Phone number field in Table1 is char(27) and Table2 is decimal(10).

Now i am using the below query to delete the phone numbers from Table1 which are present in Table2:

del
From Table1
Where Phone_Number In (
sel
Phone_Number From Table2)

The query works fine, except in the cases like if Table1 has a Phone_Number of max 27 characters, I get the error "Bad Character in format or data of Table1"

Any idea how to resolve this?

8 REPLIES
Teradata Employee

Re: Bad character in format or data

Hello,

Can you give an example?

Regards,

Adeel
Enthusiast

Re: Bad character in format or data

can you post the DDLs ? probably the data types are diff ?
Enthusiast

Re: Bad character in format or data

Table 1 would be having data as :
1) Alphanumeric characters for phone numbers
2) Spaces in data

Both will result in failure of character to numeric conversion.
rgs
Enthusiast

Re: Bad character in format or data

It is probably trying to convert the char phone number to decimal in order to the comparison. It can’t convert it to a number if it has other than numeric digits in it, therefore it gets the error (how would you expect it do to that?). So what I suggest is that you cast the decimal version to a char string and compare the two strings instead. It would probably still not do what you want, but it won’t error. What you need to do is to cleanup the char version of the phone number so that it has only numeric data.
Fan

Re: Bad character in format or data

While I am trying to run the following query .
SELECT
(case
when d.So_Qty is NULL then NULL
when ctr.So_Qty = 0 then NULL
else ctr.So_Qty end) Soq

I am getting the following error
2620 The format or data contains a bad character.

Could any one please guide me on this?

Thanks in Advance...

Vidhi

Re: Bad character in format or data

compare string to string ctr.So_Qty='0'

Re: Bad character in format or data

if the intention is to compare the numeric , filter alphanums and cast before comparing values ( assuming ctr.So_Qty is char data type)
Fan

Re: Bad character in format or data

Thanks sagujjar

This solution worked
"compare string to string ctr.So_Qty='0' "