checking for invalid or junk or special characters on numeric field

General
Enthusiast

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?????

3 REPLIES
Enthusiast

Re: checking for invalid or junk or special characters on numeric field

you can also think of using regexp_similar:

example:

select * from your_tab where regexp_similar(abc,'[^0-9]+','c')=1

Junior Supporter

Re: checking for invalid or junk or special characters on numeric field

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.

Enthusiast

Re: checking for invalid or junk or special characters on numeric field

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