VARCHAR join issue with character length

[jdighton]

I have 2 queries I need to join on location code.   Location code is alphanumeric between 4 - 6 characters.

In query one I'm using a case statement for location code so I'm having an issue with incompatible data types.

In both queries I'm casting them to a VARCHAR:      cast ( [LocationCode] ,  varchar(6) )

The join will work for locations with 6 characters but will not work with locations with 4 or 5 characters.

Any way I can get this join to work?

THANKS!

[bus_pass_man]

How does it not work?  That you are getting an error message complaining about the incompatible data types of your location code columns? Is this prior to you attempting to do the casting?  Are you casting both columns? What are the data types of the columns?  What do you mean by not work for locations with 4 or 5 characters?

[jdighton]

I'm not getting an error message but when I complete the join the field is blank because it cannot find a match.  I'm assuming it's due to incompatible data types.

If I remove the cast - I get no matches in the join.
If I add the cast (varchar(6))- I only get matches on location codes with 6 characters.

The data in both columns is a string so I'm not sure why it's not finding a match.

[bus_pass_man]

1.  If the location code columns are alphanumeric values then I would expect them to be text data types by default, so why would you be casting them to text?
2.  What is the data type of the columns?
3.  When you view the data for the two query subjects, do you see values for the columns which correspond to what you would want them to be?
4.  Can you give examples of what the data is for each column, both before and after the casting.  Especially for the things with fewer than 6 characters.
5.  Where did you get this data from?  A DB?  Uploaded files? If the latter, where is the data in it from?

[dougp]

Could it be trailing spaces causing problems?  Try

Code Select Expand

trim ( both , cast ( [LocationCode] ,  varchar(6) ) )