• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

SQL Derived Number

Hi,

In SQL

I have 2 int columns in the same table that need to be concatenated to produce a unique 14 digit number.  Concatenating the 2 int's will produce a unique number everytime (as one of them is a primary key) but the issue is if the int's are large they may exceed the 14 digit requirement.

Hope you can help

nutnut
0
nutnut
Asked:
nutnut
  • 2
1 Solution
 
sachinpatil10dCommented:
Try this

declare @Digit1 int = 20111010
declare @Digit2 int = 10000001

select convert(bigint,convert(nvarchar,@Digit1) + convert(nvarchar,@Digit2))

Open in new window

0
 
sachinpatil10dCommented:
The bigint data type is intended for use in cases where integer values might exceed the range supported by the int data type
0
 
nutnutAuthor Commented:
Thanks but this may exceed the 14 digit requiremnt
0
 
deightonCommented:
spotted a problem

if you add

111 to 1111

you get the same as adding

1111 to 111

even if the first is unique, the combination may not be

how about

declare @ikey int;
declare @some_int int;

select @ikey = 100000, @some_int = 99;

select RIGHT('0000000000' + cast(@ikey as varchar(10)),10) + RIGHT('0000' + cast(@some_int as varchar(10)),4)

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now