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 make (int) Identity field showed like (0001) ?

Hi,

How to make (int) Identity auto increment field showed like

0001

0002

0003

0004

...

Lew:

To perform the formatting you ask about will require a little code and will require converting your IDENTITY column to VARCHAR or NVARCHAR -- something like:

right('000'+convert(varchar(4),anIdentity),4)

For example:

declare @.anIdentityTable table (anIdentity integer identity)
insert into @.anIdentityTable default values

select anIdentity,
right('000'+convert(varchar(4),anIdentity),4) formattedIdentity
from @.anIdentityTable

-- anIdentity formattedIdentity
-- -- --
-- 1 0001

How to loop through a table

Hi,
I have the following table X
create table x
(
a int
)
;
insert x
values(1);
insert x
values(2);
insert x
values(3);
Is there a way to extract each a in table X to get the following print
result?
x is 1
x is 2
x is 3
Thanks a lot!!
Michael
"Michael" <michaelnx@.gmail.com> wrote in message
news:1176324884.807557.48050@.n76g2000hsh.googlegro ups.com...
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
There is, but why would you want to loop?
Simply do a select.
select 'x is ' + cast(a as char(2)) from x;

> x is 1
> x is 2
> x is 3
>
> Thanks a lot!!
> Michael
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
> x is 1
> x is 2
> x is 3
> Thanks a lot!!
> Michael
select 'x is ' + convert(varchar(20),a)
from x
order by a
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
> On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
>
>
>
>
>
>
> select 'x is ' + convert(varchar(20),a)
> from x
> order by a
> Denis the SQL Menacehttp://sqlservercode.blogspot.com/- Hide quoted text -
> - Show quoted text -
Thanks a lot!! The reason why I want to loop through the table is that
I simplified the problem. I need to loop through the table to get
exactly what I want. Anyone could show me that?
Thanks!!
Michael
|||On 12 Apr, 14:06, "Michael" <michae...@.gmail.com> wrote:
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
> Thanks!!
> Michael
>
That's an unwise assumption. In my experience it's untrue at least
99.99% of the time so do not assume that you will need to loop through
the table row by row unless you have had an expert opinion to that
effect. Maybe you consider you are an expert but the fact that you are
asking this question at all suggests that you should take a much more
cautious approach - for example you could post an accurate description
of the problem to see if anyone can suggest alternative solutions.
What you are asking for is called a cursor. Look up DECLARE CURSOR in
Books Online. Cursors can be very bad news in the wrong hands and the
stock advice is that you should avoid cursors and write set-based code
instead wherever possible.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1176383189.058521.159980@.e65g2000hsc.googlegr oups.com...
> On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
Perhaps you should post exactly what you want then. We can only answer the
questions posted.
GENERALLY, looping through a table should be and can be avoided at all
costs.
But, take a look at CURSORS in the Books Online in SQL Server.

> Thanks!!
> Michael
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

How to loop through a table

Hi,
I have the following table X
create table x
(
a int
)
;
insert x
values(1);
insert x
values(2);
insert x
values(3);
Is there a way to extract each a in table X to get the following print
result?
x is 1
x is 2
x is 3
Thanks a lot!!
Michael"Michael" <michaelnx@.gmail.com> wrote in message
news:1176324884.807557.48050@.n76g2000hsh.googlegroups.com...
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
> x is 1
> x is 2
> x is 3
>
> Thanks a lot!!
> Michael
>
Replied in .programming
Please do not multi-post.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1176324884.807557.48050@.n76g2000hsh.googlegroups.com...
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
There is, but why would you want to loop?
Simply do a select.
select 'x is ' + cast(a as char(2)) from x;

> x is 1
> x is 2
> x is 3
>
> Thanks a lot!!
> Michael
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
> x is 1
> x is 2
> x is 3
> Thanks a lot!!
> Michael
select 'x is ' + convert(varchar(20),a)
from x
order by a
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
> On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> select 'x is ' + convert(varchar(20),a)
> from x
> order by a
> Denis the SQL Menacehttp://sqlservercode.blogspot.com/- Hide quoted text -
> - Show quoted text -
Thanks a lot!! The reason why I want to loop through the table is that
I simplified the problem. I need to loop through the table to get
exactly what I want. Anyone could show me that?
Thanks!!
Michael|||On 12 Apr, 14:06, "Michael" <michae...@.gmail.com> wrote:
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
> Thanks!!
> Michael
>
That's an unwise assumption. In my experience it's untrue at least
99.99% of the time so do not assume that you will need to loop through
the table row by row unless you have had an expert opinion to that
effect. Maybe you consider you are an expert but the fact that you are
asking this question at all suggests that you should take a much more
cautious approach - for example you could post an accurate description
of the problem to see if anyone can suggest alternative solutions.
What you are asking for is called a cursor. Look up DECLARE CURSOR in
Books Online. Cursors can be very bad news in the wrong hands and the
stock advice is that you should avoid cursors and write set-based code
instead wherever possible.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1176383189.058521.159980@.e65g2000hsc.googlegroups.com...
> On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
Perhaps you should post exactly what you want then. We can only answer the
questions posted.
GENERALLY, looping through a table should be and can be avoided at all
costs.
But, take a look at CURSORS in the Books Online in SQL Server.

> Thanks!!
> Michael
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

How to loop through a table

Hi,
I have the following table X
create table x
(
a int
)
;
insert x
values(1);
insert x
values(2);
insert x
values(3);
Is there a way to extract each a in table X to get the following print
result?
x is 1
x is 2
x is 3
Thanks a lot!!
Michael"Michael" <michaelnx@.gmail.com> wrote in message
news:1176324884.807557.48050@.n76g2000hsh.googlegroups.com...
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
> x is 1
> x is 2
> x is 3
>
> Thanks a lot!!
> Michael
>
Replied in .programming
Please do not multi-post.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1176324884.807557.48050@.n76g2000hsh.googlegroups.com...
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
There is, but why would you want to loop?
Simply do a select.
select 'x is ' + cast(a as char(2)) from x;
> x is 1
> x is 2
> x is 3
>
> Thanks a lot!!
> Michael
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have the following table X
> create table x
> (
> a int
> )
> ;
> insert x
> values(1);
> insert x
> values(2);
> insert x
> values(3);
> Is there a way to extract each a in table X to get the following print
> result?
> x is 1
> x is 2
> x is 3
> Thanks a lot!!
> Michael
select 'x is ' + convert(varchar(20),a)
from x
order by a
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
> On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
>
>
> > Hi,
> > I have the following table X
> > create table x
> > (
> > a int
> > )
> > ;
> > insert x
> > values(1);
> > insert x
> > values(2);
> > insert x
> > values(3);
> > Is there a way to extract each a in table X to get the following print
> > result?
> > x is 1
> > x is 2
> > x is 3
> > Thanks a lot!!
> > Michael
> select 'x is ' + convert(varchar(20),a)
> from x
> order by a
> Denis the SQL Menacehttp://sqlservercode.blogspot.com/- Hide quoted text -
> - Show quoted text -
Thanks a lot!! The reason why I want to loop through the table is that
I simplified the problem. I need to loop through the table to get
exactly what I want. Anyone could show me that?
Thanks!!
Michael|||On 12 Apr, 14:06, "Michael" <michae...@.gmail.com> wrote:
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
> Thanks!!
> Michael
>
That's an unwise assumption. In my experience it's untrue at least
99.99% of the time so do not assume that you will need to loop through
the table row by row unless you have had an expert opinion to that
effect. Maybe you consider you are an expert but the fact that you are
asking this question at all suggests that you should take a much more
cautious approach - for example you could post an accurate description
of the problem to see if anyone can suggest alternative solutions.
What you are asking for is called a cursor. Look up DECLARE CURSOR in
Books Online. Cursors can be very bad news in the wrong hands and the
stock advice is that you should avoid cursors and write set-based code
instead wherever possible.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1176383189.058521.159980@.e65g2000hsc.googlegroups.com...
> On Apr 11, 7:57 pm, "SQL Menace" <denis.g...@.gmail.com> wrote:
>> On Apr 11, 4:54 pm, "Michael" <michae...@.gmail.com> wrote:
>>
>>
>> > Hi,
>> > I have the following table X
>> > create table x
>> > (
>> > a int
>> > )
>> > ;
>> > insert x
>> > values(1);
>> > insert x
>> > values(2);
>> > insert x
>> > values(3);
>> > Is there a way to extract each a in table X to get the following print
>> > result?
>> > x is 1
>> > x is 2
>> > x is 3
>> > Thanks a lot!!
>> > Michael
>> select 'x is ' + convert(varchar(20),a)
>> from x
>> order by a
>> Denis the SQL Menacehttp://sqlservercode.blogspot.com/- Hide quoted
>> text -
>> - Show quoted text -
> Thanks a lot!! The reason why I want to loop through the table is that
> I simplified the problem. I need to loop through the table to get
> exactly what I want. Anyone could show me that?
Perhaps you should post exactly what you want then. We can only answer the
questions posted.
GENERALLY, looping through a table should be and can be avoided at all
costs.
But, take a look at CURSORS in the Books Online in SQL Server.
> Thanks!!
> Michael
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html