none
SQL_VARIANT_PROPERTY returning column values not row values RRS feed

  • Question

  • Hi,

    Not sure how to really describe my problem, but here is a go!

    I would like to test the precision of each value in a numeric column. I have used the function SQL_VARIANT_PROPERTY which seems really good. However, it return the value set in the column definition, what is happening at the row level.

    I have created a test script to describe my problem.

    CREATE TABLE Test (CL1 Numeric (10,2))
    INSERT INTO Test
    SELECT 10.1 UNION ALL
    SELECT 10.11


    SELECT

    SQL_VARIANT_PROPERTY(CL1,'Precision') AS PrecisionResult FROM dbo.Test

    PrecisionResult
    10
    10

    The results I want to see are

    PrecisionResult
    3
    4

    Thanks,

    Ben


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Wednesday, November 9, 2016 6:33 PM

Answers

  • Hi,

    Not sure how to really describe my problem, but here is a go!

    I would like to test the precision of each value in a numeric column. I have used the function SQL_VARIANT_PROPERTY which seems really good. However, it return the value set in the column definition, what is happening at the row level.

    I have created a test script to describe my problem.

    CREATE TABLE Test (CL1 Numeric (10,2))
    INSERT INTO Test
    SELECT 10.1 UNION ALL
    SELECT 10.11


    SELECT

    SQL_VARIANT_PROPERTY(CL1,'Precision') AS PrecisionResult FROM dbo.Test

    PrecisionResult
    10
    10

    The results I want to see are

    PrecisionResult
    3
    4

    Thanks,

    Ben


    Mr Shaw... One day I might know a thing or two about SQL Server!

    I found the answer I needed on http://www.sqlservercentral.com/scripts/PRECISION/91108/

    Here is my interpretation.

    SELECT LEN(FLOOR(CL1))+LEN(CAST(REVERSE(PARSENAME( CL1,1)) AS INT)) AS Precision ,
    LEN(CAST(REVERSE(PARSENAME( CL1,1)) AS INT)) AS scale,
    CL1 FROM dbo.Test

    Thanks :)


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Wednesday, November 9, 2016 11:15 PM

All replies

  • Hi,

    Not sure how to really describe my problem, but here is a go!

    I would like to test the precision of each value in a numeric column. I have used the function SQL_VARIANT_PROPERTY which seems really good. However, it return the value set in the column definition, what is happening at the row level.

    I have created a test script to describe my problem.

    CREATE TABLE Test (CL1 Numeric (10,2))
    INSERT INTO Test
    SELECT 10.1 UNION ALL
    SELECT 10.11


    SELECT

    SQL_VARIANT_PROPERTY(CL1,'Precision') AS PrecisionResult FROM dbo.Test

    PrecisionResult
    10
    10

    The results I want to see are

    PrecisionResult
    3
    4

    Thanks,

    Ben


    Mr Shaw... One day I might know a thing or two about SQL Server!

    I found the answer I needed on http://www.sqlservercentral.com/scripts/PRECISION/91108/

    Here is my interpretation.

    SELECT LEN(FLOOR(CL1))+LEN(CAST(REVERSE(PARSENAME( CL1,1)) AS INT)) AS Precision ,
    LEN(CAST(REVERSE(PARSENAME( CL1,1)) AS INT)) AS scale,
    CL1 FROM dbo.Test

    Thanks :)


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Wednesday, November 9, 2016 11:15 PM
  • Hi Mr Shaw,

    Glad to hear that you've solved the problem. You could help mark it as answer. Your cooperation and support are highly appreciated.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 14, 2016 3:51 AM
    Moderator