Posted by Patti on 12/12/06 16:53

I can't change the actual table, but I can create a stored proc that
inserts it correctly into another table. I didn't even think to do
that (**duh**)! Thank you very much for your assistance!


Ed Murphy wrote:
> 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]