asked on

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

sachinpatil10d

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))

Open in new window

nutnut

ASKER

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

sachinpatil10d

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

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

Pratima

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)

Paul_Harris_Fusion

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
-- pad with leading zeros
--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)

nutnut

ASKER

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

sachinpatil10d

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
-- pad with leading zeros
--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

Open in new window

nutnut

ASKER

Same problem

sachinpatil10d

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

sachinpatil10d

Output

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

34567893456789
34567993456799

BartVx

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).

ASKER CERTIFIED SOLUTION

BartVx

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nutnut

ASKER

Perfect thanks

Paul_Harris_Fusion

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)