Posted by othellomy on 12/13/06 04:08

This might work:
SELECT Loan#
FROM Table1
WHERE patindex('%[2,13,1]%',Codes) > 0

Patti wrote:
> I am struggling with converting a certain varchar column into an int.
> I have a table that has 2 fields - one field holds the loan number and
> the other field holds the codes associated with that loan number.
> Here's some example data:
>
> Loan# Codes
> 11111 24-13-1
> 22222 1
> 33333 2-9
>
> I need to check the Codes field for certain code numbers. The Select
> statement I'd like to use is:
>
> SELECT Loan#
> FROM Table1 WHERE Codes IN (2, 13, 1)
> /*My desired results is that all loans from the above example would be
> selected because they all have one of these codes*/
>
> Of course I cannot use the above statement because the Codes field is a
> varchar. And if I put single quotes around the numbers in my IN
> statement I don't get the desired results; the fields with multiple
> codes are excluded.
>
> But how do I convert this varchar to an int? A simple convert or cast
> statement doesn't work. I've looked all over the web to find how to do
> this, but have not been able to figure it out. Any help would be much
> appreciated.

[Back to original message]