sql command for substring a numeric value (mismatch error)

[Troy1]

When i have string field i can do a substring like below:

substring("firstname",1,20) as "firstname"

however when i try the same command on numeric field i get mismatch error.

what is the sql command within qlikview (not a qlikview function) to substrin a numeric field.

[preminqlik]

use mid(fieldname,1,20)       

[Troy1]

gives me error mid does not work.

[#4]

just say substring(text(<your numeric field>),1,20)

[Troy1]

does not work. I tried using below and it works when i do 1000 records but when it goes beyond it gives me read error,

substring(cast(PhoneNumber as VARCHAR),1,15) as phonenumber,

[senpradip007]

Could you upload sample qvw?

[#7]

Substring(PhoneNumber, 1,15) as phonenumber

why don't you write like this ?

is there any error in the data so you have to cast it into varchar first ?

[PrashantSangle]

Hi,

If possible Post your script.

It will be better to us.

Regards

[jonathandienst]

Hi

Assuming that this is a SQL command (ie after a SQL SELECT phrase), then

     substring(cast(PhoneNumber as VARCHAR(15)),1,15) as phonenumber,

or even

     cast(PhoneNumber as VARCHAR(15)) as phonenumber,

should work.

HTH

Jonathan

[Azharyasin]

hi

i hv a numeric data field of 3 digit and intend to use ist two digits as substring pl guide

thanx

Azharyasin