MS SQL Server/compare numeric field against alphanumeric field to get the common rows
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.
--The key is numeric
-- The data type is memo
From DBO..Table..File f
join dbo..table2..descriptions t on t.task = f.task
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
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)
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.