Prev: DBCC Memory Status and Query Plan
Next: xp_cmdshell sql server 2000
From: Patti on 12 Dec 2006 10:04
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.

From: Ed Murphy on 12 Dec 2006 11: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.
From: Patti on 12 Dec 2006 11: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.

From: Erland Sommarskog on 12 Dec 2006 17:43
Ed Murphy (emurphy42(a)socal.rr.com) writes:
> 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(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: othellomy on 12 Dec 2006 23: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.

 |  Next  |  Last
Pages: 1 2
Prev: DBCC Memory Status and Query Plan
Next: xp_cmdshell sql server 2000