1

I am working on an to solve a problem that I have a textbox that accepts any type of data but I want to cast the data to decimal(12,9) or any data type can accept this number as an example 42.56926437219384

here is the problem the user can enter any kind of data like characters or integer

1st case I want to handle if the data entered as integer:

DECLARE @num DECIMAL(12,9) = 444444444444444
SELECT CONVERT(DECIMAL(12,9),@num)

I think if it is characters will handle it in the solution and assign validation for the textbox.

how can I handle the integer part?

A.D.
  • 2,257
  • 2
  • 13
  • 24
Bishoy Ezzat
  • 147
  • 2
  • 14
  • with DECIMAL(12,9) you have 3 integer places and 9 places for decimals. Your input won't even fit in DECIMAL(12,0) since it has more than 12 integer places used. I am not sure what is your goal with this free-text field, first please share it with us. – Pred Dec 13 '17 at 09:31

1 Answers1

4

When you specify it as DECIMAL(12,9) this means your number can have up to 12 digits (excluding . ) and out of that 9 digits will be after decimal part. Which means the maximum value that you can store here is 999.999999999.

in your sample scenario above, the number has got 15 integer parts which are too high than the scope of your variable, as you can do either of the following

  1. Change the size of variable from DECIMAL(12,9) to higher integer precision like DECIMAL(25,9)
  2. Add a validation on your application to restrict the number of characters that the user can enter
Jayasurya Satheesh
  • 6,581
  • 3
  • 15
  • 31
  • oh thank you ... but what if it is 30 characters of 4 e.g. 444444444444444444444444444444 ?? – Bishoy Ezzat Dec 13 '17 at 09:38
  • @BishoyEzzat What is the purpose of this field? – Pred Dec 13 '17 at 09:39
  • that's why I told you, it's safer to limit the input string length. – Jayasurya Satheesh Dec 13 '17 at 09:39
  • @JayasuryaSatheesh longitude and latitude can accept a large scale of decimals – Bishoy Ezzat Dec 13 '17 at 09:41
  • Check here, I think this might help https://stackoverflow.com/questions/15965166/what-is-the-maximum-length-of-latitude-and-longitude – Jayasurya Satheesh Dec 13 '17 at 09:43
  • 2
    in this case you should not accept anything else, but what is inside the valid ranges of longitude and latitude. Those should NEVER be greater than 180 or 90 in absolute value. Why do you want to store an invalid value? Restrict your input to the valid ranges and make your life easier. – Pred Dec 13 '17 at 09:47