Bad Character in Varchar defined column

UDA

Bad Character in Varchar defined column

Hi,

Fee column is defined as varchar which consists of both alphanumeic and decimal values.I need to identify alphanumeric values and delete from table.
How can we acheive this and what will be the easiest way?

Thanks in advance

regards,
kartheek M
7 REPLIES

Re: Bad Character in Varchar defined column

Delete from $table where $column between 'a' and 'z' or $column between 'A' and 'Z';

Note:
the above query will delete all the alphanumeric values except the one starts with numeric value.

Re: Bad Character in Varchar defined column

Hi,

I will explain the problem more clearly

Lets assume FEE column conatins values like

Fee
27M50.00
10.0
203.2

Among these I need to identify values which contain alphabetic or special characters also and delete them like 27M50.00.
Ram,I tried above query,didn't work.

Thank you
Teradata Employee

Re: Bad Character in Varchar defined column

Try this:

del from tablename
where fee like any
(
'%A%'
,'%B%'
,'%C%'
,'%D%'
,'%E%'
,'%F%'
,'%G%'
,'%H%'
,'%I%'
,'%J%'
,'%K%'
,'%L%'
,'%M%'
,'%N%'
,'%O%'
,'%P%'
,'%Q%'
,'%R%'
,'%S%'
,'%T%'
,'%U%'
,'%V%'
,'%W%'
,'%X%'
,'%Y%'
,'%Z%'
)
;

Re: Bad Character in Varchar defined column

Hi,

i got a question here,

Does this query works to obtain the desired result set,

DELETE FROM TABLE_NAME
WHERE Fee LIKE '%A-Z%';

Re: Bad Character in Varchar defined column

Hi Kartik,

Please explain your problem more clearly.
Do you want to delete entire row if your condition satisfies? or
you want to delete only column value?

Re: Bad Character in Varchar defined column

Hi Karthik,

You can use the below query to delete all alpha numeric entries

DELETE FROM $tablename WHERE
LOWER($columnnane) (CASESPECIFIC) <>UPPER($columnnane) (CASESPECIFIC);

Regards,
Arun Prakash

Re: Bad Character in Varchar defined column

Hi Guys,

Thanks to all of you for your answers.

sRavanSarma
I tiried your query,it's not giving the answer.

queries from arunprakash and jitender are working fine like
DELETE FROM $tablename WHERE
LOWER($columnnane) (CASESPECIFIC) <>UPPER($columnnane) (CASESPECIFIC)

Regards,
Kartheek.M