Join char(14) to char(15)? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Join char(14) to char(15)?

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-05-13 : 16:53:23

Hey all-
I need to do a join on two tables that have different field sizes. Can someone help me out?

table one
somekey int,
charge_number char(14),
more_crap char(15)

table two
somekey int,
chrg_num char(15),
chrg_desc char(30)

How can I make this work?

Thanks,
Doug

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-13 : 17:28:03

you could try
Select * from
[table one] t1
inner join [table two] t2
on t1.charge_number = left(t2.table two, 14)

or you may have to convert them both to varchar(15) to do the join

Graham

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-13 : 22:56:24

Choose your poison. :) You might want to test this though before you implement the suggestion above:

DECLARE @table1 TABLE(col1 VARCHAR(14))
DECLARE @table2 TABLE(col1 VARCHAR(15))

INSERT @table1(col1)
SELECT 'A1111111111111'
UNION ALL
SELECT 'A1111111111112'
UNION ALL
SELECT 'A1111111111113'

INSERT @table2(col1)
SELECT 'A1111111111111'
UNION ALL
SELECT 'A11111111111121'
UNION ALL
SELECT 'A1111111111113 '
UNION ALL
SELECT 'A11111111111134'

SELECT t1.col1, t2.col1
FROM
@table1 t1
INNER JOIN @table2 t2 ON CAST(t1.col1 AS VARCHAR(15)) = t2.col1

SELECT t1.col1, t2.col1
FROM
@table1 t1
INNER JOIN @table2 t2 ON t1.col1 = LEFT(t2.col1,14)

SELECT t1.col1, t2.col1
FROM
@table1 t1
INNER JOIN @table2 t2 ON t1.col1 = t2.col1

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-14 : 01:09:04

You could change the tables so that they have the correct structure.
As you have it one will have to be converted so any index on that field cannot be used.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources