Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

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
0
nutnut
Asked:
nutnut
  • 5
  • 4
  • 2
  • +2
1 Solution
 
sachinpatil10dCommented:
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

0
 
nutnutAuthor Commented:
The key requirement is for the derived number to be 14 digits everytime so this solution will not work unfortunately
0
 
sachinpatil10dCommented:
declare @Digit1 int = 20111010
declare @Digit2 int = 10000001

select convert(bigint,left(convert(nvarchar,@Digit1) + convert(nvarchar,@Digit2),14))
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Pratima PharandeCommented:
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
 
Paul_Harris_FusionCommented:
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)
0
 
nutnutAuthor 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
 
sachinpatil10dCommented:
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

0
 
nutnutAuthor Commented:
Same problem
0
 
sachinpatil10dCommented:
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
 
sachinpatil10dCommented:
Output

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


34567893456789
34567993456799
0
 
BartVxCommented:
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
 
BartVxCommented:
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
 
nutnutAuthor Commented:
Perfect thanks
0
 
Paul_Harris_FusionCommented:
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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