How to find bad character in a data in SQL assistance

General
Highlighted

How to find bad character in a data in SQL assistance

Hi ,

I have two tables, Table A and Table B. Having the same record in both the tables. 

 

--------------Sample data
--Table A
Id Description
1 123ABC456|~|No|~|NEED_TO_UPDT

--Table B
Id Description
1 123ABC456|~|No|~|NEED_TO_UPDT

/* Above records are for example purpose, Business data is almost similar to it
Plese suggest if you ever had faced same challenges.
*/

--------Query
SEL * FROM
A INNER JOIN B ON A.DESCRIPTION = B.DESCRIPTION;
--------Result I am getting
A.Id A.Description B.Id B.Description
1 123ABC456|~|No|~|NEED_TO_UPDT 1 123ABC456|~|No|~|NEED_TO_UPDT
1 123ABC456|~|No|~|NEED_TO_UPDT 1 123ABC456|~|No|~|NEED_TO_UPDT

I am getting two duplicate records. However, applying TRIM in joining condition is giving the appropriate result of one.

 

Please suggest some reason for this weird kind of behavior. ( *I suspect the issue is due to bad character, alas not able to find it out anyway.)

 

Thanks in advance...

 

 

1 REPLY
Teradata Employee

Re: How to find bad character in a data in SQL assistance

Hi Sarada Rout,

 

for one string, you can check the ascii code of every character using this sample code :

with recursive cte_recurs (str, lvl, one_char, remain_str) as
(
select description
     , 1 (integer)
     , SubString(description from 1 for 1)
     , SubString(description from 2)
  from TableA
 where Id = 1
 union all
select str
     , lvl + 1
     , SubString(remain_str from 1 for 1)
     , SubString(remain_str from 2)
  from cte_recurs
 where character_length(remain_str) > 0
)
  select one_char
       , Ascii(one_char)
    from cte_recurs
order by lvl;