All Forums Analytics
Meenakshi S 3 posts Joined 09/08
12 Nov 2008
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:delFrom Table1Where 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?

Adeel Chaudhry 773 posts Joined 04/08
12 Nov 2008

Hello,Can you give an example?Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

joedsilva 505 posts Joined 07/05
12 Nov 2008

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

amit_dwh 13 posts Joined 06/06
13 Nov 2008

Table 1 would be having data as :1) Alphanumeric characters for phone numbers2) Spaces in dataBoth will result in failure of character to numeric conversion.

rgs 106 posts Joined 02/07
13 Nov 2008

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.

VidhiS 2 posts Joined 08/11
08 Aug 2011

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

sagujjar 4 posts Joined 09/10
09 Aug 2011

compare string to string ctr.So_Qty='0'

sagujjar 4 posts Joined 09/10
09 Aug 2011

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

VidhiS 2 posts Joined 08/11
09 Aug 2011

Thanks sagujjar

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

You must sign in to leave a comment.