topic Re: sql command for substring a numeric value (mismatch error) in QlikView

sql command for substring a numeric value (mismatch error)

Troy1 — Tue, 22 Jul 2014 01:56:51 GMT

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 — Tue, 22 Jul 2014 02:15:34 GMT

use mid(fieldname,1,20)

Troy1 — Tue, 22 Jul 2014 03:26:34 GMT

gives me error mid does not work.

Tue, 22 Jul 2014 03:30:13 GMT

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

Troy1 — Tue, 22 Jul 2014 03:40:04 GMT

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 — Tue, 22 Jul 2014 03:59:03 GMT

Could you upload sample qvw?

Tue, 22 Jul 2014 04:00:41 GMT

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 — Tue, 22 Jul 2014 04:21:22 GMT

Hi,

If possible Post your script.

It will be better to us.

Regards

jonathandienst — Tue, 22 Jul 2014 05:23:21 GMT

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 — Mon, 04 May 2020 10:21:15 GMT

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

thanx

Azharyasin