LEN function, know number of characters of a number

Asked

Viewed 189 times

9

In the example below the result of the query instead of returning 7 is returned 5.

DECLARE @valor money = 10.6879

SELECT LEN(@Valor)

The impression I get is that when the number is the type money , is considered only 2 decimal places.

What function would I use to return the exact number of characters of a money-type number?

sql sql-server function

3 answers

3

First create the function

CREATE FUNCTION dbo.countDigits(@A float) RETURNS tinyint AS
BEGIN
declare @R tinyint
IF @A IS NULL 
   RETURN NULL
set @R = 0
while @A - str(@A, 18 + @R, @r) <> 0
begin
   SET @R = @R + 1
end
RETURN @R
END
GO

Then just use it

SELECT LEN(cast(10.6879 as decimal)) + dbo.countDigits(10.6879) +  + 1 
//número antes da virgula + Número de casas depois da vírgular + 1 = 7
  • 1

    This solved my problem, through this function I know exactly the number of characters of a number. Converting the number to float would also solve my problem?

2

The solution to your problem is simple:

DECLARE @Valor MONEY = 10.6879

SELECT LEN(CONVERT(FLOAT, @Valor)) -- 7

The difference to the example of @Leonardobonetti is the type of conversion data (FLOAT instead of DECIMAL(6, 4)).

2

Convert using CONVERT for the guy DECIMAL (reference here): 

DECLARE @valor money = 10.6879

SELECT LEN(CONVERT(decimal(6,4),@Valor)); //7

  • This solution works, but how would I do when I don’t know the number of decimal places in the number? In this solution you are stating that the number will have 4 decimal places. I would like to know the number of characters of a number, regardless of the number of decimals.

  • @Ademiltonmarcelonunes as much as you have already worked for André, I will look for the solution to your question ;)

  • I will remove the right from André’s reply to await his reply.

Browser other questions tagged sql sql-server function

You are not signed in. Login or sign up in order to post.