Link to home
Start Free TrialLog in
Avatar of nutnut
nutnutFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of sachinpatil10d
sachinpatil10d
Flag of India image
Try this

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

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of sachinpatil10d
sachinpatil10d
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nutnut
nutnut
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks but this may exceed the 14 digit requiremnt
Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image
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)