How to get max presicion/width and scale stored in column ?

[Former Member]

Hi, Experts

There is a table column defined as numeric (126,38).

How to get max precision/width and scale of values actually inserted into the column ?

Thanks in advance

[hans-juergen_schwindke · Accepted solution]

Hi,

I would use string functions to solve this. I know this is not really fast. Currently I don't have an IQ so I can't provide the statements.

So for the "whole length": convert / cast the value to char and then use length or char_length function.

For scale you can use the same approach, find the "." with charindex, create a substring, and then the length function again.

I think the following would be faster: subtract column_value - int(column_value) and then convert / cast to string and use length function again.

Best regards,

Juergen

[tayeb_hadjou]

Hi,

Try select below based on Juegen's idea. It works ONLY for values without zeros digits in the right.

Example for input value 1234567.123 :

select  (CHAR_LENGTH(cast(1234567.123 as varchar(150)) ) -1) as Num_Precision,  (CHAR_LENGTH( cast(1234567.123 as varchar(150)) )  - LOCATE(1234567.123, '.') ) as Num_Scale FROM iq_dummy

Result :

NumPrecision, NumScale

10, 3

For numbers like 1234567.000 or 1234567.01000, you may need modify the statement or find out another one.

The reason is cast function truncates the right zeros when converting to varchar :

cast(1234567.000 as varchar(150)) gives 1234567

cast(1234567.01000 as varchar(150)) gives 1234567.01

Regards, Tayeb.