Converting a varchar to int

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.Patti wrote:

Quote:

Originally Posted by

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_..._ 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

Quote:

Originally Posted by

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.|||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:

Quote:

Originally Posted by

Patti wrote:
>

Quote:

Originally Posted by

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_..._ 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
>

Quote:

Originally Posted by

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.

|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

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

Seems like some % are missing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This might work:
SELECT Loan#
FROM Table1
WHERE patindex('%[2,13,1]%',Codes) 0

Patti wrote:

Quote:

Originally Posted by

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.

|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

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

>
Seems like some % are missing.

Yes, of course you're right, should be

WHERE '-'+Codes+'-' like '%-2-%'
OR '-'+Codes+'-' like '%-13-%'
OR '-'+Codes+'-' like '%-1-%'

but the approach of "use a stored procedure to copy the data to a
better-normalized table" is probably better. (Oh, and that new
table should probably have an index on the Code column.)