Posted by Ed Murphy on 12/12/06 16:25

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

A classic violation of first normal form:

http://en.wikipedia.org/wiki/First_normal_form#Multiple_meaningful_values_in_a_single_field

If at all possible, change your table to look like this:

Loan# Code
11111 24
11111 13
11111 1
22222 1
33333 2
33333 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*/

and then this simply becomes

SELECT Loan#
FROM Table1
WHERE Code in (2, 13, 1)

That said, if fixing the 1NF violation will take a while, then in the
short term, you can do something like the following. (You can't convert
Codes to int, because e.g. '24-13-1' isn't a number. Instead, you must
convert the search terms from int to varchar.)

SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'

Also, you may need SELECT DISTINCT, in case some Loan#s have multiple
matches and you only want to include them once.

[Back to original message]