Help with a join

Question

Post reply

Help with a join

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
August 20, 2014 at 7:28 am

#290047

Hi there,
I am trying to join two tables that visibly doesn't have any thing unique as they come from separate systems. I believe I have a solution but am not sure how to write the TSQL to achieve what I'm after.
Database 1
We have a "Unique Transaction ID".
Examples being -
1. 4461
2. 14130
3. 23891
Database 2
When these transactions are imported into another system these change to the following -
1. CP000000004461
2. CP000000014130
3. CP000000023891
So I need to find a way in my first table to make 4461 to CP000000004461 and 23891 to CP000000023891, so that I can use that as a unique link to link to the second table.
It's the leading zeroes I'm having issues with but the length will always be 14.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

How about something like this?

declare @x table (TransId varchar(10))

insert @x

(TransId)

values ('4461'),

('14130')

select TransId

,'CP' + right('000000000000' + TransId, 12)

from @x x

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

Quick thought, this can easily be stuffed

😎

;WITH BASE_ID AS

( SELECT BID FROM

(VALUES (4461),(14130),(23891)) AS X(BID)

)

SELECT

BI.BID

,STUFF('CP000000000000'

,14 - LEN(CAST(BI.BID AS VARCHAR(12)))

,LEN(CAST(BI.BID AS VARCHAR(12)))

,CAST(BI.BID AS VARCHAR(12))

) AS UNIQUE_LINK

FROM BASE_ID BI

Results

BID UNIQUE_LINK

----------- ---------------

4461 CP000000044610

14130 CP000000141300

23891 CP000000238910

niladri.primalink SSCommitted Points: 1751 More actions · Answer 3

Recently I have faced this kind of situation in my organization. Temp table and with cte is the best option in my opinion when there is no relation between the tables or unique values.

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 4

Thanks guys for the help so far.

I've discovered that what I thought was a potential link actually isn't so after all your advice it's not going to help me in this scenario.

That being said I think I have found another potential to link my two data sets together.

Example I table 1 we have "708698"

In table 2 we have "Internet Card Payment Auth Code: 708698"

Is there anyway in SQL that you could say join 708698 to Internet Card Payment Auth Code: 708698 by looking in the table 2 field to match the 708698?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Ryan Keast (8/21/2014)
Thanks guys for the help so far.
I've discovered that what I thought was a potential link actually isn't so after all your advice it's not going to help me in this scenario.
That being said I think I have found another potential to link my two data sets together.
Example I table 1 we have "708698"
In table 2 we have "Internet Card Payment Auth Code: 708698"
Is there anyway in SQL that you could say join 708698 to Internet Card Payment Auth Code: 708698 by looking in the table 2 field to match the 708698?

Is all the data in the column you wish to join to like this: Internet Card Payment Auth Code: nnnnnn where nnnnnn is the value to be joined?

If yes, can you add a persisted computed column to table 2 that extracts the Internet Card Payment Auth Code: ? If so, you could then index the persisted column and join to it.