How can I query a varchar(x) value for the number of decimal places

Question

I have a varchar(250) ParameterValue that I would like to check the number of decimal places in.

This is the line of code that I cannot get working:

where RIGHT(CAST(ParameterValue as DECIMAL(10,5)), ParameterValue), 1) != 0

The code below is where the line of code is used:

select * 
INTO    #ParamPrecision
from Data_table
where RIGHT(CAST(ParameterValue as DECIMAL(10,5)), ParameterValue), 1) != 0
AND     ParameterBindStatus = 0

UPDATE  a
SET     a.ParameterBindStatus = 5
FROM    Data_table, #ParamPrecision b
WHERE   a.SQLParameterId = b.SQLParameterId

INSERT  Log_table
        (
        SQLBatchId,
        SQLProcess,
        Error,
        SQLError_Message,
        ParametersSent
        )
SELECT  SQLBatchId,
        'sp_ReadParametersToBindData',
        1,
        'Invalid parameter value sent from MES',
        'some parameter info'
FROM    #ParamPrecision

SELECT  *
INTO    #UnBoundPrompt
FROM    Data_table
WHERE   DATEADD(DAY, 1, SQLTimeStamp) < GETDATE()
AND     ParameterBindStatus = 0

UPDATE  a
SET     a.ParameterBindStatus = 99
FROM    Data_tablea, #UnBoundPrompt b
WHERE   a.SQLParameterId = b.SQLParameterId

INSERT  Log_table
        (
        SQLBatchId,
        SQLProcess,
        Error,
        SQLError_Message,
        ParametersSent
        )
SELECT  SQLBatchId,
        'sp_ReadParametersToBindData',
        1,
        'Parameter download timeout',
        'some parameter info'
FROM    #UnBoundPrompt

If the check for decimal places is not satisfied, the next select statement checks if the parameter timestamp is active for more than 1 day. If this is satisfied, a log entry is made.

If the number of decimal places exceeds 4, then I want to set the ParameterBindStatus = 5 and update the log table.

I have changed the code as follows to allow me to confirm the rest of the code and that works but the code does not execute when trying to detect number of decimal places.

select * 
INTO    #ParamPrecision
from Data_table
where ParameterValue > '1500'
AND     ParameterBindStatus = 0

What's the result you get, and what did you expect? Please take the [tour] and read [ask] to learn how to improve your question, then boil the code down to a [mcve]. — Murphy, Sep 17 '19 at 11:08
I have updated my original post with more detail. However, I did search other posts earlier but I have found a solution in another post, see [link](https://stackoverflow.com/questions/37024739/get-the-number-of-digits-after-the-decimal-point-of-a-float-with-or-without-dec). I changed `where RIGHT(CAST(ParameterValue as DECIMAL(10,5)), ParameterValue), 1) != 0` to `where CHARINDEX('.',REVERSE(CONVERT(VARCHAR(50), ParameterValue, 128))) -1 > @NoOfParameterPrecision` — Dave, Sep 17 '19 at 13:11
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using (the non standard use of `#` and `dateadd()` seems to indicate you are using SQL Server) — , Sep 17 '19 at 13:35

score 0 · Answer 1 · answered Sep 17 '19 at 13:59

this may help with your precision problem - I've laid it out as a table so you can see each step of the transformation but you can easily see the pattern :) essentially you just reverse the string and truncate it. All steps included (can be done faster) - you may/may not need to add a bit for the case that there is no decimal point.

--setup
create table test 
(stringVal varchar(250));

insert into test values
('12.3456'),
('1.2345678'),
('12'),
('0.123')

--query
SELECT stringVal,
        Reverse(CONVERT(VARCHAR(50), stringVal, 128)) as reversedText
      , Cast(Reverse(CONVERT(VARCHAR(50), stringVal, 128)) as float) as float
      , Cast(Cast(Reverse(CONVERT(VARCHAR(50), stringVal, 128)) as float) as bigint) as bigint
      , len(Cast(Cast(Reverse(CONVERT(VARCHAR(50), stringVal, 128)) as float) as bigint)) as decimalPrecision
FROM   test

How can I query a varchar(x) value for the number of decimal places

1 Answers1