Using IsNum in SQL SELECT

[Black_Hole]

Hello all,

When I run my script I have an error in the following lines in particular:

SQL SELECT *

FROM Table_Cus

WHERE SUBSTRING(NUM_CUS,1,1)='S' AND ISNUMERIC(SUBSTRING(NUM_CUS,2,LEN(NUM_CUS)-1));

In attached file, I screened the detail of my error.

Please could you help me to identify what's wrong in my where clause.

Thank you in advance for your help.

[Anil_Babu_Samineni · Accepted solution]

Can i convert this to Qlik, If so - Here u go?

Load * Where Mid(NUM_CUS,1,1)='S' AND ISNUM(Mid(NUM_CUS,2,LEN(NUM_CUS)-1));
SQL SELECT * FROM Table_Cus

[peterwh]

Hello,

in the error message I see, that you're using a DB2 database. I've made a search on "Db2 for i SQL reference" and it seems there is no "Isnumeric" function.

On "www.experts-exchange.com" I found the following solution, maybe this will help you:

you can use the translate function and translate each numeric character to blank,
then use trim and check if you get the empty string
for example

select ....
from
where rtrim(ltrim(translate(your_string,' ','1234567890'))) = ''

Kind regards

Peter

[Black_Hole]

Hello @Anil_Babu_Samineni , @peterwh ,

Thank you both for your help.

@Anil_Babu_Samineni :  I try it and it's run successfully.

@peterwh :  I understand why  sometimes there are functions which are not identified in my QVW. I will consider the connection when I will use the functions implemented in QVW.