JOINing on CHAR fields of different field lengths

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

General SQL Server Forums

Database Design and Application Architecture

JOINing on CHAR fields of different field lengths

Author

Topic

Rye Guy
Starting Member

3 Posts

Posted - 2007-11-30 : 15:13:40

Can someone comment on why joining two tables on CHAR fields of different lengths would generate unexpected results?

I had an issue where I ran an update that used an inner join on two tables. The field I used in the join was char(50) in one table and char(13) in another table. The result gave bad matches. After changing the field types both to varchar(30), the problem was eliminate.

Any comments on this would be appreciated.

Rye Guy

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-30 : 15:16:22

I suspect it is because CHAR fields get padded to the full length so effectively the same value wouldn't match exactly between two fields with different lengths due to the padding. VARCHAR does not do this.

I tested this theory and it does not seem to be the case, at least for me. Can you post the query you are using that produces the incorrect results?

Future guru in the making.

Rye Guy
Starting Member

3 Posts

Posted - 2007-12-08 : 00:26:17

It was essentially this...

update A set anyfield = "something"
from parent A
inner join child B
on A.field1 = B.field1

In my case field1 was char(50) in the parent table and char(13) in the child table.

Rye Guy

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-08 : 10:52:13

works for me:


declare @t1 table (id1 int identity(1,1) primary key, bleh char(50) not null)
declare @t2 table (id2 int identity(1,1) primary key, blah char(13) not null)

insert @t2(blah)
select 'abcdefghijklm' union all
select '1111111111111' union all
select '2222222222222'

insert @t1(bleh)
select 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx' union all
select '11111111111111111111111111111111111111111111111111' union all
select '22222222222222222222222222222222222222222222222222' union all
select '1111111111111' union all
select '2222222222222'


update A set bleh = 'something'
from @t1 A
inner join @t2 B
on A.bleh = B.blah

select * from @t1

-- results are:
1	abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx
2	11111111111111111111111111111111111111111111111111
3	22222222222222222222222222222222222222222222222222
4	something                                         
5	something

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources