i want to store 65 digit number in database which

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

i want to store 65 digit number in database which

Author

Topic

girishhande
Starting Member

26 Posts

Posted - 2009-07-30 : 07:40:22

i want to store 65 digit number in database which datatype to use..
i dont want nvarchar

gkh

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-30 : 08:54:59

65 digits, huh? what does this "number" represent? How will you use it?

Be One with the Optimizer
TG

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-07-30 : 10:06:42

Reminds me...
http://overt.org/2009/02/03/this-t-shirt/

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 11:01:11

quote:
Originally posted by blindman

Reminds me...
http://overt.org/2009/02/03/this-t-shirt/

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

You always post stuff that get's blocked here

I don't even know what you would call this "number"

DECLARE @number varchar(65)
SELECT @number = RIGHT(REPLICATE(',999',17),65)
SELECT @number AS [number]

number
-----------------------------------------------------------------
9,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999

(1 row(s) affected)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-07-30 : 11:07:49

quote:
Originally posted by X002548
You always post stuff that get's blocked here

...so that you'll have something to look forward to when you get home.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-30 : 11:34:27

quote:
Originally posted by X002548
I don't even know what you would call this "number"

DECLARE @number varchar(65)
SELECT @number = RIGHT(REPLICATE(',999',17),65)
SELECT @number AS [number]

number
-----------------------------------------------------------------
9,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999

(1 row(s) affected)

It's even bigger than that, you included the commas as part of the 65 digits :)

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 12:08:50

OK...details...details

This is correct I think....


DECLARE @number varchar(87)
SELECT @number = '-'+RIGHT(REPLICATE(',999',22),86)
SELECT @number AS [number]

number
---------------------------------------------------------------------------------------
-99,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999

(1 row(s) affected)

Enough Rope?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-30 : 12:14:21

quote:
Originally posted by girishhande

i want to store 65 digit number in database which datatype to use..
i dont want nvarchar

gkh

You should probably use VARCHAR; there is no TSQL numeric datatype that can hold that many digits. You should also be aware that you will not be able to use any TSQL math on a number like that.

If you don't mind the loss of precision, you might be able to use a FLOAT datatype.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 12:18:45

The most I can get using a true numeric datatype is 38

DECLARE @d decimal(38)

SELECT @d = CONVERT(decimal(38),LEFT(REPLICATE('1234567890',4),38))

PRINT @d

number
---------------------------------------
12345678901234567890123456789012345678

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 12:21:11

I think float get's you less

DECLARE @d float(53)

SELECT @d = CONVERT(float(53),LEFT(REPLICATE('1234567890',6),53))
SELECT CONVERT(float(53),LEFT(REPLICATE('1234567890',6),53))

PRINT @d

EDIT: Even this doesn't help

DECLARE @d float(53)

SELECT @d = CONVERT(float(53),LEFT(REPLICATE('1234567890',6),53))

SELECT CONVERT(varchar(53),@d)

number
-----------------------------------------------------
1.23457e+052

(1 row(s) affected)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 12:27:48

How could you stuff the commas into a straight varchar number after every 3 bytes?

DECLARE @number varchar(65)
SELECT @number = RIGHT(REPLICATE('1234567890',7),65)
SELECT @number AS [number]

number
-----------------------------------------------------------------
67890123456789012345678901234567890123456789012345678901234567890

(1 row(s) affected)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-30 : 16:29:41

You can use BINARY.

N 56°04'39.26"
E 12°55'05.63"

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 16:32:37

quote:
Originally posted by Peso

You can use BINARY.

N 56°04'39.26"
E 12°55'05.63"

To what end?

Got Milk?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-07-30 : 17:05:15

If you are going to store it as varchar, convert it to hexadecimal first so you need fewer characters.
Better yet, use base 256.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources