0

I want to know how we can find of length of Numeric field in sql. What will be the command / Query so that we can find length of perticular field in table.

E.g.: For below table:

Column1 Column2
1          1111  
2            11
3         44444      
4            11
5           111

From above example I want to see Record/fields in column "column2" which has numeric length '2'.

What query should I execute to achieve this?

abhishek
  • 325
  • 1
  • 9
  • 19

3 Answers3

0

Use LEN function

select * 
from table 
where len(column2) = 2

This will not work when you want to include data like '0.2' or similar values

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

You can also use the POWER function and < and >= to maintain SARGability

WITH tbl (column1, column2) AS(
    SELECT 1, 1111 UNION ALL
    SELECT 2, 11 UNION ALL
    SELECT 3, 44444 UNION ALL
    SELECT 4, 11 UNION ALL
    SELECT 5, 111
)
SELECT *
FROM tbl
WHERE
    column2 < POWER(10, 2)
    AND column2 >= POWER(10, 1)
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
0

You can't as stated here

Length and digits are both properties of a physical representation of a number in a specific base, i.e. a String.

So you have to convert numeric field to varchar/char first then find the length as follows:

SQL Server:

select * 
from table 
where len(CONVERT (varchar(10), colunm2 )) = 2

MySQL:

select * 
from table 
where length(convert(column2,char)) = 2
Community
  • 1
  • 1
Muhammad Muazzam
  • 2,810
  • 6
  • 33
  • 62