0

What is the best way to store a number in SQL SERVER so that if i have a number like...

192,234.35

It will get stored as such?

Will Numeric as DataType do the trick?

BobSki
  • 1,531
  • 2
  • 25
  • 61
  • 1
    you shouldn't be worrying about storing a number with the thousands separator, that should be just for displaying the data – Lamak Feb 08 '17 at 19:15
  • Please distinguish between storing data and displaying them. You can store the number as 192234.35 (a numeric type). And you can display them as you want. – DVT Feb 08 '17 at 19:15
  • @GurV well I am taking a value from excel spreadsheet and storing it as string and want to be able to compare it to the value in sql server. – BobSki Feb 08 '17 at 19:15
  • 2
    Dont save it as string, take lot more space and you cant add two string Matematically. – Juan Carlos Oropeza Feb 08 '17 at 19:16
  • @JuanCarlosOropeza - don't need to add them - need to compare them – BobSki Feb 08 '17 at 19:16
  • 1
    @BobSki don't store them as a string... ever... you can compare digits without storing them as a string. You will save yourself a ton of heartache by treating data with appropriate datatypes. – S3S Feb 08 '17 at 19:17
  • Same thing. A 10 digits number mean you need to compare 10 chars/bytes, a number only need 4 to 8 bytes top – Juan Carlos Oropeza Feb 08 '17 at 19:17
  • @JuanCarlosOropeza - what would my dataType need to be in SqlServer - simply Numeric? – BobSki Feb 08 '17 at 19:20
  • Numeric, decimal, it all depends... but NOT VARCHAR() – S3S Feb 08 '17 at 19:20
  • check this post http://stackoverflow.com/questions/759401/is-there-any-difference-between-decimal-and-numeric-in-sql-server – Juan Carlos Oropeza Feb 08 '17 at 19:43

3 Answers3

3

Don't store numerical data as text. I prefer decimal but anything is almost better than text. Here's a simple idea to consider.

declare @someDecimal decimal (8,2) = 192234.35
declare @someText varchar(16) = '192,234.35'

--here's how you would have to compare your text to an actual number
select 'True' where @someDecimal = convert(decimal(8,2),replace(@someText,',',''))

--then if you wanted to do any math on it, you would also have to convert it.
select convert(decimal(8,2),replace(@someText,',','')) * 2
--versus just
select @someDecimal * 2

--If you want to display it, use format...
select FORMAT(@someDecimal,'#,0.0000')

Ordering your data is where you would find big problems since it's done character by character. Again, this is why you don't store it as text.

declare @tableOfText table (d varchar(16))
insert into @tableOfText (d)
values
('001'),
('010'),
('10'),
('110'),
('011'),
('1'),
('12')

select * from @tableOfText order by d desc
S3S
  • 24,809
  • 5
  • 26
  • 45
2

Recommended way would be to store the number without commas as a DECIMAL type (assuming that in question, 192,234.35 is a single number) and for displaying it to user, just add commas to make the number readable.

However, you are free to store it like a VARCHAR, if you are not going to do any mathematical operation on it (like average, sum, etc.)

utsav_deep
  • 621
  • 6
  • 23
1

I would suggest to store it as decimal.

In SQL Server 2012+ we have the format function, which can be used for formatting with thousand separator 

FORMAT(@d,'#,0.0000')

In prior version we can take recourse to some custom function

Chandan Rai
  • 9,879
  • 2
  • 20
  • 28