We have some customer names in the Database table with special character, because of which many of the queries are failing since its can't able to find the match with the string having special character.
Is there any way to find out such customer list having special character in the string, so that we can update those and solve the problem ??
I have used DUMP() function just to show individual char separately.
SQL> select substr(the_customer_name, 1, 22), substr(upper(the_customer_name), 1, 22), dump(the_customer_name, 1017)
from cp_solution where solution_number='CON091021142150';
THE_CUSTOMER_NAME UPPER_THE_CUSTOMER_NAME DUMP(THE_CUSTOMER_NAME)
---------------------- ------------------------ --------------------------------------------------------------------------------
Crème de la crème test CRHME DE LA CRHME TEST Typ=1 Len=22 CharacterSet=US7ASCII: C,r,e8,m,e, ,d,e, ,l,a,,c,r,e8,m,e, ,t,e,s,t
SQL> select substr(the_customer_name, 1, 22), substr(upper(the_customer_name), 1, 22), dump(upper(the_customer_name), 1017)
from cp_solution where solution_number='CON091021142150';
THE_CUSTOMER_NAME UPPER_THE_CUSTOMER_NAME DUMP(THE_CUSTOMER_NAME)
---------------------- ------------------------ --------------------------------------------------------------------------------
Crème de la crème test CRHME DE LA CRHME TEST Typ=1 Len=22 CharacterSet=US7ASCII: C,R,H,M,E, ,D,E, ,L,A, ,C,R,H,M,E, ,T,E,S,T
The customer name I mentioned above (check the_customer_name filed) have special char does not belong to 'Standard ASCII Set'. These char are part of 'Extended ASCII Set'
Interesting point to notice is when I use upper() those special char getting converted to normal char (e.g. 'è' became 'H').
We also have problem with special char whihch are belongs to 'Standard ASCII Set'.
SQL> select customer_name, dump(customer_name, 1017) from cp_dbor_customer where cp_customer__id=16141920 ;
CUSTOMER_NAME DUMP(CUSTOMER_NAME,1017)
------------------------------------------ ------------------------------------------------------------------------------------------------------------------------
MBB Management LLC DBA Donovans Irish Pub Typ=1 Len=42 CharacterSet=US7ASCII: M,B,B, ,M,a,n,a,g,e,m,e,n,t, ,L,L,C, ,D,B,A, ,D,o,n,o,v,a,n,^R,s, ,I,r,i,s,h, ,P,u,b
Can anyone suggest one way to pull those customer name from database ?