All Forums General
rakeshsapbo 36 posts Joined 04/13
02 Oct 2014
checking for invalid or junk or special characters on numeric field

Hi All,
 
i am trying to find out the Invalid characters (Junk/special characters) on the numeric column it's data type is BIGINT

AND (DBC.ABC LIKE '%&%'

OR DBC.ABC LIKE '%^%'

OR DBC.ABC LIKE '%+%'

OR DBC.ABC LIKE '%)%'

OR DBC.ABC LIKE '%$%'

OR DBC.ABC LIKE '%(%'

OR DBC.ABC LIKE '%*%'

OR DBC.ABC LIKE '%~%')
DBC - databasename
ABC - numeric field with data type "BIGINT"
 
my ask - i need to check for the Invalid characters (Junk/special characters) on the numeric column (ABC) it's data type is BIGINT
 
when i fired the query in SQL ASST
i am getting the below error
 
"SELECT Failed 3544 : Partial string matching requires character operands"
do we need to cast that particular column to achieve this ?
can you please suggest?????

Rakesh Reddy G
Raja_KT 1246 posts Joined 07/09
02 Oct 2014

you can also think of using regexp_similar:
example:
select * from your_tab where regexp_similar(abc,'[^0-9]+','c')=1

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

CarlosAL 512 posts Joined 04/08
02 Oct 2014

If the column DBC.ABC is actually BIGINT what you are trying to do is nonsense.
BIGINT columns cannot contain such 'junk' characters.
You cannot use LIKE operand on numeric columns.
Cheers.
Carlos.

Raja_KT 1246 posts Joined 07/09
02 Oct 2014

Yes Carlos true. I think what he means to say is target field is BIGINT and source is varchar maybe.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.