How to make a safe varchar() to int conversion

I'm selecting a big group of records for output, and I need to convert a
couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
myintfield from ...)
Problem is, some erroneous data has non-numeric characters in it, and SQL
Server kills the whole SELECT, outputting no rows (!!!).
Is there any way to get SQL to just put NULL or 0 in for erroneous data -
the way you can use SET ARITHABORT to have it ignore numeric errors and keep
processing?
Thanks!
- NevynHello Nevyn,
One of the best way's I've found to do this is to use a LIKE clause in your
select statement
SELECT * FROM myTable WHERE numCol NOT LIKE '%[a-z]%'
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm selecting a big group of records for output, and I need to convert
> a couple columns from varchar to int. (SELECT CAST(mycharfield as int)
> as myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and
> SQL Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous
> data - the way you can use SET ARITHABORT to have it ignore numeric
> errors and keep processing?
> Thanks!
> - Nevyn
>|||You can just add
"where isnumeric(col)=1"
or
"where col like '%[^0-9]%'"
to your query
-oj
"Nevyn Twyll" <astian@.hotmail.com> wrote in message
news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...
> I'm selecting a big group of records for output, and I need to convert a
> couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
> myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and SQL
> Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous data -
> the way you can use SET ARITHABORT to have it ignore numeric errors and
> keep processing?
> Thanks!
> - Nevyn
>|||That would be "where col NOT like '%[^0-9]%'"
Gert-Jan
oj wrote:
> You can just add
> "where isnumeric(col)=1"
> or
> "where col like '%[^0-9]%'"
> to your query
> --
> -oj
> "Nevyn Twyll" <astian@.hotmail.com> wrote in message
> news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...|||Tks for the correction.
-oj
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42015A90.8863CB3B@.toomuchspamalready.nl...
> That would be "where col NOT like '%[^0-9]%'"
> Gert-Jan
>
> oj wrote:

how to know how many records in each table and output to textfile?

Hi,
Is there a fast way to know how many records in every tables?
Thanks a lot!
regards,
florencelee
This is an approximate rowcount, depending on when SQL Server last updated
its statistics, but it's fast.
SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
BY 1
On the output, you could just copy/paste in Query Analyzer, or set up a
command line task and redirect the output to a file e.g.
osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e7YZ39o0EHA.4004@.tk2msftngp13.phx.gbl...
> Hi,
> Is there a fast way to know how many records in every tables?
> --
> Thanks a lot!
> regards,
> florencelee
>
|||florencelee,
Or if you want accurate info use this query:
exec sp_msforeachtable 'select ''?'' as tablename, count(*) as
[rowcount] from ?'
Note: if your tables are large this query will take significant amount
of time.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Peter Yeoh wrote:
> This is an approximate rowcount, depending on when SQL Server last updated
> its statistics, but it's fast.
> SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
> BY 1
> On the output, you could just copy/paste in Query Analyzer, or set up a
> command line task and redirect the output to a file e.g.
> osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
>

how to know how many records in each table and output to textfile?

Hi,
Is there a fast way to know how many records in every tables?
Thanks a lot!
regards,
florenceleeThis is an approximate rowcount, depending on when SQL Server last updated
its statistics, but it's fast.
SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
BY 1
On the output, you could just copy/paste in Query Analyzer, or set up a
command line task and redirect the output to a file e.g.
osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e7YZ39o0EHA.4004@.tk2msftngp13.phx.gbl...
> Hi,
> Is there a fast way to know how many records in every tables?
> --
> Thanks a lot!
> regards,
> florencelee
>|||florencelee,
Or if you want accurate info use this query:
exec sp_msforeachtable 'select ''?'' as tablename, count(*) as
[rowcount] from ?'
Note: if your tables are large this query will take significant amount
of time.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Peter Yeoh wrote:
> This is an approximate rowcount, depending on when SQL Server last updated
> its statistics, but it's fast.
> SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
> BY 1
> On the output, you could just copy/paste in Query Analyzer, or set up a
> command line task and redirect the output to a file e.g.
> osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
>

how to know how many records in each table and output to textfile?

Hi,
Is there a fast way to know how many records in every tables?
--
Thanks a lot!
regards,
florenceleeThis is an approximate rowcount, depending on when SQL Server last updated
its statistics, but it's fast.
SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
BY 1
On the output, you could just copy/paste in Query Analyzer, or set up a
command line task and redirect the output to a file e.g.
osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e7YZ39o0EHA.4004@.tk2msftngp13.phx.gbl...
> Hi,
> Is there a fast way to know how many records in every tables?
> --
> Thanks a lot!
> regards,
> florencelee
>|||florencelee,
Or if you want accurate info use this query:
exec sp_msforeachtable 'select ''?'' as tablename, count(*) as
[rowcount] from ?'
Note: if your tables are large this query will take significant amount
of time.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Peter Yeoh wrote:
> This is an approximate rowcount, depending on when SQL Server last updated
> its statistics, but it's fast.
> SELECT OBJECT_NAME(id), rowcnt FROM sysindexes WHERE indid IN (0, 1) ORDER
> BY 1
> On the output, you could just copy/paste in Query Analyzer, or set up a
> command line task and redirect the output to a file e.g.
> osql -E -d <dbname> -Q "SELECT ...." > TableRowCount.txt
>