Convert varchar to int problem

31 Aug 2006 9:28 PM

dev648237923

I have a table with two varchar fields (a and b) like this:

a, b
-----
1, 10
1, 20
2, some_text
2, some_more_text

I want to sum up all the type answers where a='1' (so from the above it
should be 10+20=30)
Field b is varchar so I do:
SELECT SUM(CONVERT(int, b))
WHERE a = '1'

***but the convert fails and complains because it sees some non-convertable
data?
I can't see why this fails -- the error message says it can not convert
some_text but I specifically say where a='1' so it should never see those
rows?
Any ideas appretiated!

Note if I said where b='10' or b='20' that would work but I never know what
will be in b so I cant look for every possibility so that is why I assumed
saying where a='1' would cover it

31 Aug 2006 9:36 PM

sloan

I'd try tagging on the isnumeric, just to make sure.

Remarks
ISNUMERIC returns 1 when the input expression evaluates to a valid integer,
floating point number, money or decimal type; otherwise it returns 0. A
return value of 1 guarantees that expression can be converted to one of
these numeric types.

This runs (doesn't return anything) on the pubs database.

select sum ( convert ( int , au_id ) ) , isnumeric(au_id) from authors
group by au_id
having isnumeric(au_id) > 0

or

select ( convert ( int , au_id ) ) , isnumeric(au_id) from authors
where isnumeric(au_id) > 0

Show quote

"dev648237923" <dev648237923@noemail.noemail> wrote in message
news:OuInzRUzGHA.4308@TK2MSFTNGP03.phx.gbl...
> I have a table with two varchar fields (a and b) like this:
>
> a, b
> -----
> 1, 10
> 1, 20
> 2, some_text
> 2, some_more_text
>
> I want to sum up all the type answers where a='1' (so from the above it
> should be 10+20=30)
> Field b is varchar so I do:
> SELECT SUM(CONVERT(int, b))
> WHERE a = '1'
>
> ***but the convert fails and complains because it sees some
non-convertable
> data?
> I can't see why this fails -- the error message says it can not convert
> some_text but I specifically say where a='1' so it should never see those
> rows?
> Any ideas appretiated!
>
> Note if I said where b='10' or b='20' that would work but I never know
what
> will be in b so I cant look for every possibility so that is why I assumed
> saying where a='1' would cover it
>
>

31 Aug 2006 9:37 PM

sloan

Oops. I should have used zip column.

select sum ( convert ( int , zip ) ) , isnumeric(zip) from authors
group by zip
having isnumeric(zip) > 0

select ( convert ( int , zip ) ) , isnumeric(zip) from authors
where isnumeric(zip) > 0

select sum ( convert ( int , zip ) ) from authors
where isnumeric(zip) > 0

Show quote

"dev648237923" <dev648237923@noemail.noemail> wrote in message
news:OuInzRUzGHA.4308@TK2MSFTNGP03.phx.gbl...
> I have a table with two varchar fields (a and b) like this:
>
> a, b
> -----
> 1, 10
> 1, 20
> 2, some_text
> 2, some_more_text
>
> I want to sum up all the type answers where a='1' (so from the above it
> should be 10+20=30)
> Field b is varchar so I do:
> SELECT SUM(CONVERT(int, b))
> WHERE a = '1'
>
> ***but the convert fails and complains because it sees some
non-convertable
> data?
> I can't see why this fails -- the error message says it can not convert
> some_text but I specifically say where a='1' so it should never see those
> rows?
> Any ideas appretiated!
>
> Note if I said where b='10' or b='20' that would work but I never know
what
> will be in b so I cant look for every possibility so that is why I assumed
> saying where a='1' would cover it
>
>

31 Aug 2006 9:45 PM

Arnie Rowland

Using a CASE statement could make this a little more 'bullet proof'.

DECLARE @MyTable table
   (    RowID       int         IDENTITY
      , ColA        varchar(20)
      , ColB        varchar(20)
   )

INSERT INTO @MyTable VALUES ( '1', '10' )
INSERT INTO @MyTable VALUES ( '1', '20' )
INSERT INTO @MyTable VALUES ( '2', 'some_text' )
INSERT INTO @MyTable VALUES ( '2', 'some_more_text' )

SELECT ColumnTotal = sum( CASE WHEN isnumeric( ColB ) = 1 THEN ColB
                               ELSE 0
                          END
                        )
FROM @MyTable

Note: this works even when the textual values are included.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"dev648237923" <dev648237923@noemail.noemail> wrote in message news:OuInzRUzGHA.4308@TK2MSFTNGP03.phx.gbl...
>I have a table with two varchar fields (a and b) like this:
>
> a, b
> -----
> 1, 10
> 1, 20
> 2, some_text
> 2, some_more_text
>
> I want to sum up all the type answers where a='1' (so from the above it
> should be 10+20=30)
> Field b is varchar so I do:
> SELECT SUM(CONVERT(int, b))
> WHERE a = '1'
>
> ***but the convert fails and complains because it sees some non-convertable
> data?
> I can't see why this fails -- the error message says it can not convert
> some_text but I specifically say where a='1' so it should never see those
> rows?
> Any ideas appretiated!
>
> Note if I said where b='10' or b='20' that would work but I never know what
> will be in b so I cant look for every possibility so that is why I assumed
> saying where a='1' would cover it
>
>

31 Aug 2006 10:29 PM

Erland Sommarskog

Arnie Rowland (ar***@1568.com) writes:
Show quote

> Using a CASE statement could make this a little more 'bullet proof'.
>
> DECLARE @MyTable table
>    (    RowID       int         IDENTITY
>       , ColA        varchar(20)
>       , ColB        varchar(20)
>    )
>
> INSERT INTO @MyTable VALUES ( '1', '10' )
> INSERT INTO @MyTable VALUES ( '1', '20' )
> INSERT INTO @MyTable VALUES ( '2', 'some_text' )
> INSERT INTO @MyTable VALUES ( '2', 'some_more_text' )
>
> SELECT ColumnTotal = sum( CASE WHEN isnumeric( ColB ) = 1 THEN ColB
>                                ELSE 0
>                           END
>                         )
> FROM @MyTable

Change isnumeric() to "ColB LIKE '%[^0-9]%'". isnumeric() is a useless
function. It tells you that a string converts to a numeric data type -
but not which.

The expression I gave will find positive integers only.

--
Erland Sommarskog, SQL Server MVP, esq***@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

31 Aug 2006 11:23 PM

Arnie Rowland

Good point Erland. I didn't consider other data irregularity issues.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns98314EB6CC0CYazorman@127.0.0.1...
> Arnie Rowland (ar***@1568.com) writes:
>> Using a CASE statement could make this a little more 'bullet proof'.
>>
>> DECLARE @MyTable table
>>    (    RowID       int         IDENTITY
>>       , ColA        varchar(20)
>>       , ColB        varchar(20)
>>    )
>>
>> INSERT INTO @MyTable VALUES ( '1', '10' )
>> INSERT INTO @MyTable VALUES ( '1', '20' )
>> INSERT INTO @MyTable VALUES ( '2', 'some_text' )
>> INSERT INTO @MyTable VALUES ( '2', 'some_more_text' )
>>
>> SELECT ColumnTotal = sum( CASE WHEN isnumeric( ColB ) = 1 THEN ColB
>>                                ELSE 0
>>                           END
>>                         )
>> FROM @MyTable
>
> Change isnumeric() to "ColB LIKE '%[^0-9]%'". isnumeric() is a useless
> function. It tells you that a string converts to a numeric data type -
> but not which.
>
> The expression I gave will find positive integers only.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@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