You are here:

MS SQL Server/compare numeric field against alphanumeric field to get the common rows

Advertisement


Question
QUESTION: Hi David,

I have one field which is numerical data type and the other is memo type. I want my query to return the all the rows that have common number in them.

Example

Select
--The key is numeric
f.Key_file,

-- The data type is memo
t.Description

From DBO..Table..File f
join dbo..table2..descriptions t on  t.task = f.task

Where
f.keyfile in (t.description)


--This is the error i get = Conversion failed when converting the nvarchar value '15582720,Japanese' to data type int.


Can you help me with this problem? I hope i made sense.

ANSWER: What version of SQL Server?

---------- FOLLOW-UP ----------

QUESTION: SQL Server 2008

Answer
Ooops. Apologies for the delay.

First, is Descriptions a VARCHAR type, or a TEXT type. There is no MEMO type in SQL. I'm assuming VARCHAR (or NVARCHAR) for now.


The "IN" operator is used to see if a given value is contained in a list of values, such as
WHERE SomeNumericCode IN (1,5,7,9,10)
or
WHERE SomeCharCode IN ('A', 'XYZ', 'DDD')

In your example, t.description is not a list.

You'll want to use the CHARINDEX function. Something like
WHERE CHARINDEX( CAST(f.keyfile AS VARCHAR(100)), t.description ) > 0

Note that as the values for keyfile get smaller, your chances of a false positive get bigger. For example, a key value of "2012" would be found in a description of
"...the item will be returned by June, 2012 at the latest".

I dont know what type of data is in Description, so this may not be an issue.  

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


David Vaughn

Expertise

Performance tuning; T-SQL syntax;

Experience

Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Education/Credentials
Truman State University

©2016 About.com. All rights reserved.