# SQL Derived Number Q2

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 NOT produce a unique number everytime, this is fine, if concatenation produces the same results on 1 or more rows in the table then they need to have the same derived 14 digit number.

Hope you can help

nutnut
###### Who is Participating?

Commented:
Using the above approach, you can google for a function that converts a number to a higher based numeric system (Base22 seems to be enough) and  concatenate the results.

See here for example:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109916
0

Commented:
Try this

The bigint data type is intended for use in cases where integer values might exceed the range supported by the int data type
``````declare @Digit1 int = 20111010
declare @Digit2 int = 10000001

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

Author Commented:
The key requirement is for the derived number to be 14 digits everytime so this solution will not work unfortunately
0

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

select convert(bigint,left(convert(nvarchar,@Digit1) + convert(nvarchar,@Digit2),14))
0

Commented:
do you want to reduse the digits ??

like this

declare @Digit1 int

set @Digit1= 20111010
declare @Digit2 int

set @Digit2=  10000001

select Left (convert(nvarchar,@Digit1) + convert(nvarchar,@Digit2),14)
0

Commented:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[BuildKey]
(
@int1 int,
@int2 int
)
RETURNS varchar(14)
AS
BEGIN
DECLARE @part1 varchar(14)
DECLARE @part2 varchar(14)

Set @part1 = Cast(@int1 as varchar)
Set @part2 = Cast(@int2 as varchar)

-- enable whicheverlines produce the desired results
--set @part1=right('000000000' + @part1,7)
--set @part2=right('000000000' + @part2,7)

-- pad with trailing zeros
set @part1=left(@part1 + '000000000',7)
set @part2=left(@part2 + '000000000',7)

RETURN (@part1+@part2)

END

go

Select dbo.BuildKey(3,2)
0

Author Commented:
Thank but this does not fully work as

Select dbo.BuildKey(123456789,123456789)
Select dbo.BuildKey(123456799,123456799)

Return the same answer when they need to return different 14 digit numbers
0

Commented:
Change the function to
``````SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[BuildKey]
(
@int1 int,
@int2 int
)
RETURNS varchar(14)
AS
BEGIN
DECLARE @part1 varchar(14)
DECLARE @part2 varchar(14)

Set @part1 = Cast(@int1 as varchar)
Set @part2 = Cast(@int2 as varchar)

-- enable whicheverlines produce the desired results
--set @part1=right('000000000' + @part1,7)
--set @part2=right('000000000' + @part2,7)

-- pad with trailing zeros
set @part1=right('000000000' + @part1 ,7)
set @part2=right('000000000' + @part2 ,7)

RETURN (@part1+@part2)

END

go
``````
0

Author Commented:
Same problem
0

Commented:
actually Paul has given the solution and has marked in comment

-- pad with trailing zeros
set @part1=right('000000000' + @part1 ,7)
set @part2=right('000000000' + @part2 ,7)

please watch the function carefully
0

Commented:
Output

Select dbo.BuildKey(123456789,123456789)
Select dbo.BuildKey(123456799,123456799)

34567893456789
34567993456799
0

Commented:
You will need to find someway to uniquely encode your integers into a 7 character string and then concatenate them. By converting them to their character representations as suggested above, this will not work.

I was looking into encoding them into their hexadecimal equivalent, but unfortunately this only works if you maximum value is 268435455 (#FFFFFFF).

0

Author Commented:
Perfect thanks
0

Commented:
nutnut,

Based on your post
> Select dbo.BuildKey(123456789,123456789)
>  Select dbo.BuildKey(123456799,123456799)
> Return the same answer when they need to return different 14 digit numbers

I think what you are trying to do may be impossible.     There are far more combinations that result from concatenating two 9 digit integers than can be contained in a 14 digit number - obvious really since you can create an 18 digit number from your two inputs.

You would need to change the data type of your key and use an alpha numeric encoding.   Here is a modified version of my previous post using Base-64 encoding (derived from http://www.vbforums.com/showthread.php?t=554886)

Drop Function dbo.BuildKey
go

Drop Function dbo.IntToBase64

go

CREATE Function dbo.IntToBase64
(@input int)
Returns varchar(7)
As

BEGIN
DECLARE @Base64 VARCHAR(MAX)
Declare @bin varbinary(MAX)

set @bin = Cast(@input as binary)

SET @Base64 = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'VARCHAR(MAX)')

RETURN right(@Base64,7)
END

go

CREATE FUNCTION [dbo].[BuildKey]
(
@int1 int,
@int2 int
)
RETURNS varchar(14)
AS
BEGIN
DECLARE @part1 varchar(14)
DECLARE @part2 varchar(14)

Set @part1 = dbo.IntToBase64(@int1)
Set @part2 = dbo.IntToBase64(@int2)

RETURN @part1+@part2

END

go

select dbo.IntToBase64(3)
select dbo.IntToBase64(2)

Select dbo.BuildKey(3,2)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.