datatype for such a long integer

Question

Post reply

datatype for such a long integer

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
February 19, 2013 at 5:30 am

#289674

Hi
I got a dataset from somewhere which I have to store in sql server. It has one column which consists on numeric values with length 116.
i.e. its an integer with 116 numbers. Not even bigint support this kind on length. I can't put it in varchar as i'll need to do sort operations on it.
Are there any alternatives for this in sql server?
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Could you do something like this?

IF object_id('s_sumar_s') IS NOT NULL

BEGIN

DROP TABLE s_sumar_s;

END;

CREATE TABLE s_sumar_s (

ID INT IDENTITY(1,1) NOT NULL,

faux_number VARCHAR(116) CONSTRAINT CK_faux_number CHECK (RIGHT('00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'+faux_number,116) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

useful_faux_number AS RIGHT('00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'+faux_number,116)

);

Then use "useful_faux_number" to sort, e.g.

INSERT INTO s_sumar_s(faux_number)

SELECT '1267'

UNION ALL SELECT '231'

UNION ALL SELECT '428973681279460182436';

SELECT * FROM s_sumar_s ORDER BY useful_faux_number ASC;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

but will the sorting work correctly on varchar datatype?

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

S_Kumar_S (2/19/2013)
but will the sorting work correctly on varchar datatype?

Yes. . . did you execute the code I produced? So long as you sort on the computed column "useful_faux_number" it will be correctly ordered.

IF object_id('s_sumar_s') IS NOT NULL

BEGIN

DROP TABLE s_sumar_s;

END;

CREATE TABLE s_sumar_s (

ID INT IDENTITY(1,1) NOT NULL,

faux_number VARCHAR(116) CONSTRAINT CK_faux_number CHECK (RIGHT('00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'+faux_number,116) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

useful_faux_number AS RIGHT('00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'+faux_number,116)

);

INSERT INTO s_sumar_s(faux_number)

SELECT '1267'

UNION ALL SELECT '231'

UNION ALL SELECT '428973681279460182436';

SELECT * FROM s_sumar_s ORDER BY useful_faux_number ASC;

The above produces: -

ID faux_number useful_faux_number

----------- -------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------

2 231 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000231

1 1267 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001267

3 428973681279460182436 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000428973681279460182436

Whereas, if I instead sort on the non-computer column: -

SELECT * FROM s_sumar_s ORDER BY faux_number ASC;

We get it sorted as characters, which is wrong: -

ID faux_number useful_faux_number

----------- -------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------

1 1267 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001267

2 231 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000231

3 428973681279460182436 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000428973681279460182436

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 4

awesome solution Cadavre!

only thing i would do is clean up the presentation with REPLICATE to get the zeros, and maybe just a simple not like '%[^0-9]%' ; what do you think of this?

IF object_id('s_sumar_s') IS NOT NULL

BEGIN

DROP TABLE s_sumar_s;

END;

CREATE TABLE s_sumar_s (

ID INT IDENTITY(1,1) NOT NULL,

faux_number VARCHAR(116) CONSTRAINT CK_faux_number CHECK (RIGHT(REPLICATE('0',120) + faux_number,116) NOT LIKE '%[^0-9]%'),

useful_faux_number AS RIGHT(REPLICATE('0',120) + faux_number,116)

);

INSERT INTO s_sumar_s(faux_number)

SELECT '1267'

UNION ALL SELECT '231'

UNION ALL SELECT '428973681279460182436';

--this should fail against the constraint.

insert into s_sumar_s(faux_number) values ('Alphabits42')

SELECT * FROM s_sumar_s ORDER BY useful_faux_number ASC;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Lowell (2/19/2013)
awesome solution Cadavre!
only thing i would do is clean up the presentation with REPLICATE to get the zeros, and maybe just a simple not like '%[^0-9]%' ; what do you think of this?

Replicate is a much better idea, didn't cross my mind. I prefer to use "LIKE" rather than "NOT LIKE" though, despite the extra code.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 6

Thanks a lot both of you. it worked. I was thinking that something like this will also work but it didn't 🙁

create table testing(ID int identity,

big_number VARCHAR(116) CONSTRAINT CK_big_number CHECK (RIGHT(REPLICATE('0',120) + big_number,116) NOT LIKE '%[^0-9]%'),

useful_big_number AS RIGHT(REPLICATE('0',120) + big_number,116)

)

insert into testing

select '16456456456456456456456456456456546546456456456456456456'

union select '2'

union select '11'

union select '1'

select * from testing order by '000000000'+big_number

Cadavre (2/19/2013)
Lowell (2/19/2013)
awesome solution Cadavre!
only thing i would do is clean up the presentation with REPLICATE to get the zeros, and maybe just a simple not like '%[^0-9]%' ; what do you think of this?
Replicate is a much better idea, didn't cross my mind. I prefer to use "LIKE" rather than "NOT LIKE" though, despite the extra code.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

sturner SSC-Insane Points: 22411 More actions · Answer 7

insert into testing

select '16456456456456456456456456456456546546456456456456456456'

union select '2'

union select '11'

union select '1'

select * from testing

order by len(big_number), big_number

The probability of survival is inversely proportional to the angle of arrival.

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 8

S_Kumar_S (2/19/2013)
Hi
I got a dataset from somewhere which I have to store in sql server. It has one column which consists on numeric values with length 116.
i.e. its an integer with 116 numbers. Not even bigint support this kind on length. I can't put it in varchar as i'll need to do sort operations on it.
Are there any alternatives for this in sql server?
thanks

What the dataset looks like may be very important here. Since, as you identified, that the 116 digit "integer" cannot be stored in any datatype in SQL Server other than a VARCHAR, I have to ask, what does the dataset actually look like? If it's coming across as a fixed width, right justified, character based column, then just import the entire "field" including the leading spaces (or zeroes) available in the dataset and your resulting column will, in fact, be sortable.

Otherwise, you'll need to do as what Cadavre suggested.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 9

No, the dataset is not of fixed length and format. there may be a length of 1 and then there may be 116. So I go by proposed solution of Cadavre.

Jeff Moden (2/20/2013)
S_Kumar_S (2/19/2013)
Hi
I got a dataset from somewhere which I have to store in sql server. It has one column which consists on numeric values with length 116.
i.e. its an integer with 116 numbers. Not even bigint support this kind on length. I can't put it in varchar as i'll need to do sort operations on it.
Are there any alternatives for this in sql server?
thanks
What the dataset looks like may be very important here. Since, as you identified, that the 116 digit "integer" cannot be stored in any datatype in SQL Server other than a VARCHAR, I have to ask, what does the dataset actually look like? If it's coming across as a fixed width, right justified, character based column, then just import the entire "field" including the leading spaces (or zeroes) available in the dataset and your resulting column will, in fact, be sortable.
Otherwise, you'll need to do as what Cadavre suggested.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.