comparing

[aspiringprog]

Hi all,

I am trying to join a table (using equijoin) on two columns that are of two different data types..one is a text and the other is a little-endian data type.

Can anyone tell me if there is any way I can do this join? I can re-create the table and change the text column to little endian also, but I am not able to find the proper data type to do it,

Any help is really appreciated.

 

[bborissov]

What is "little endian"?
Can you post Structure opf the table some example data and desired result?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[aspiringprog]

well..little-endian data type means if there is a number like '01' in the column, little-endian converts it into a '1' and neglects the leading zero. So even though the number is actually two digits, it shows up as a single digit if the leading digit is a zero(or of lesser significant value).

So i have to make an inner join(sorry, I misspoke and said equijoin the first time)on two columns, where one is a text and the other is little-endian.

I can alter the table and convert the text column to little-endian, but I am not sure of the datatype to use.Or if I can make some calculations on the fly, while I am performing the join, that would work as well..however I am not sure what calculations I could perform.

Hope this helps..

 

[bborissov]

Why Text?
Can't you use varchar()?

If 8000 bytes (the max varchar length in SQL 2000) is not enough and you use SQL Server 2005 you could use varchar(max) column, varchar(max) could keep up to 2^31-1 bytes.
As I said w/o seeing the data you keep in these columns I couldn't be more specific.




Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

is little-endian data an int perhaps?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[monksnake]

You can do it if there are equal values in the two columns you are comparing, just do a convert on 1 or both of the columns to give them the same data type.

select * from table1 inner join table2 on 
convert(char(3), table1.intColumn) = table2.charColumn

[small]"Mom........MEATLOAF!!!! F***!!!!"[/small]
<.

 

[aspiringprog]

Hi...yes little endian is an int..i figured the datatype is smallint..
and I am trying to do an explicit convert on the columns like you guys mentioned..but somehow i am still getting an error..

Syntax error during explicit conversion of VARCHAR value '0

I am not sure if this makes any sense to you..but this is the error i am getting...the query I have runs for a while,so it is working, but invariable ends up with this error displaying only a few records

 

[bborissov]

Don't try to convert varchar data to int, because in varchar field you could have a numbers which exceed int type (2^31-1)
Do the opposite convertion int to varchar, and if you want leading zeros:

SELECT ......
       FROM .....
INNER JOIN Table2 ON Table2.VarcharField =
                     RIGHT(REPLICATE('0',LEN(Table2.VarcharField))+CAST(Table1.IntgereField as varchar(LEN(Table2.VarcharField))),LEN(Table2.VarcharField))

NOT TESTED AT ALL!

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP