Join Varchar and Integer fields - JustinDo

I am trying to do query that join varchar and integer fields at Select
statement.
I guess I have to convert integer field into varchar field to make it work.

How do I go about doing it (in coding)?

Appreciate for feedback.

Join Varchar and Integer fields - Mike C#

It would probably be more efficient and robust if you joined integer columns
to integer columns.  If you join a varchar to an integer column the varchar
will be implicitly cast to integer.  This adversely affects performance, and
if your varchar column contains any non-numeric data (like the strings "N/A"
or "Unknown" or something) your query will error.  Since the varchar column
is being joined to an integer column, is it safe to assume that the varchar
column contains strings that all represent integer values?  If so, why is
the varchar column varchar and not integer?

Join Varchar and Integer fields - Tibor Karaszi

I have this feeling that Mike read the word "join" as in JOIN, such as:

FROM t1.varcharCol
INNER JOIN t2.intCol

For above we *do* have a performance hit, since most probably indexes cannot be used to support the
join.


For addition or concatenation in the select list (as below), I'd say that any possible performance
hit is probably not measurable.

SELECT intCol + charCol, ...
FROM tbl
...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Join Varchar and Integer fields - rpresser

I'm guessing you have it backwards, Mike: Justin doesn't want to add
12 and '15' and return 27; he wants to take 12 and 'Z' and return
'12Z'.

The solution:

SELECT convert(varchar(10),some_integer_column) + some_varchar_column
FROM some_table

Join Varchar and Integer fields - TheSQLGuru

Tibor, I think an index scan would be possible, just not a seek.  Could be
wrong on that.

Justin, here is sample code, assuming this is what you want

select *
FROM t1.varcharCol
INNER JOIN CAST(t2.intCol as varchar(xx))

make SURE you cast the int type to the exact length as the varchar, so at
least an index on THAT column can be used.  Also be aware that if t1.varchar
isn't sufficiently long you could lose data.  ints can be up to 9 digits
plus a - sign.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

Join Varchar and Integer fields - Mike C#

Yup, my bad.  I didn't realize he was talking about string concatenation; I
thought he wanted to perform a join with a varchar column and an integer
column in the ON clause.

Join Varchar and Integer fields - Tibor Karaszi

Hmm, yes, probably. There has been some advances here in 2005 (compared to 2000) so I'm not sure
exactly when and when not indexes can be scanned/searched in these circumstances. But we agree that
there certainly is a *risk* of a lesser performant query compared to having the right (same)
datatypes in the first place... :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Join Varchar and Integer fields - Tibor Karaszi

That is exactly how I read the question as well. Well, Justin should now have some thoughts for both
cases. :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Join Varchar and Integer fields - JustinDo

Thank you very much.
The query you provided was what I was looking for, and it worked for me.

Justin