SQL join query

[original PM]

There are only two things that might indicate there is a God.

The first is your liver is six times as big as it needs to be.

The second is MS Access.

It is just some static data I need to load into a new system and update every 6 months or so.

As I currently do not use Access not thinking there is any point bringing another system into the mix.....

Excel lookups it is although I will play around with CAST if I get the chance.

[oscarose]

Are you 100% the VARCHAR field contains valid numbers? As well as looking at the CASE, I'd be checking using the ISNUMERIC function...

[WTFH]

Are you 100% the VARCHAR field contains valid numbers? As well as looking at the CASE, I'd be checking using the ISNUMERIC function...

...and strip off any spaces.

CAST(LTRIM(RTRIM(thatfield)) as INT)

[original PM]

Are you 100% the VARCHAR field contains valid numbers? As well as looking at the CASE, I'd be checking using the ISNUMERIC function...

Yes

Generally it is the cost centre which i am trying to join on but the I am trying to get the query to run using databases from 2 different systems

In 1 this is a varchar in the other it is an int - so same data but different types.

Same restriction on entry as well - but managed at front end not db level.

[LondonManc]

The big danger is data quality - the VARCHAR field might not contain just the equivalent integers.

Just run a select cast(table.varcharcol as int) from table on the table with the varchar field.
If it breaks, you've got data integrity issues; if it doesn't, go with the cast in the join as others have suggested. A WITH (NOLOCK) hint may speed it up if you've got performance issues.

[woohoo]

A WITH (NOLOCK) hint may speed it up if you've got performance issues.

I don't know what kind of data OP has or how often its updated. But I would suggest if it's financial or needs to be accurate and is updated frequently then he at least understands the downsides of NOLOCK.