I ran the following simple select statement :
select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted =
'20041123'
Then I got the following error message after several thousand records were
fetched.
Syntax error converting the varchar value 'ZAER "' to a column of data
type int.
What I like to do is convert that column which is char to int or select
everything with the value 'ZAER' and copy to another table and then convert.
I did the first and updated, copied the data back and ran the select and
still got the same error. Can someone help please? Thank you.
James
How do you plan to convert the value 'ZAER' to an INT? And why do you have
non-integer strings mixed in with integer amounts in your 'amount' column?
I would recommend that you clean up your data and re-define the column as
INT to avoid these types of issues in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> I ran the following simple select statement :
> select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted =
> '20041123'
> Then I got the following error message after several thousand records were
> fetched.
> Syntax error converting the varchar value 'ZAER "' to a column of data
> type int.
> What I like to do is convert that column which is char to int or select
> everything with the value 'ZAER' and copy to another table and then
convert.
> I did the first and updated, copied the data back and ran the select and
> still got the same error. Can someone help please? Thank you.
> James
|||What I wanted to do was remove all the non-integer strings first with an
update to '0' before converting. Do you know what I can do?
James.
"Adam Machanic" wrote:
> How do you plan to convert the value 'ZAER' to an INT? And why do you have
> non-integer strings mixed in with integer amounts in your 'amount' column?
> I would recommend that you clean up your data and re-define the column as
> INT to avoid these types of issues in the future.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> convert.
>
>
|||Here's what I would do:
UPDATE tcase
SET amount= '0'
WHERE PATINDEX('%[^0-9]%', RTRIM(LTRIM(amount))) > 0
OR amount IS NULL
ALTER TABLE tcase
ALTER COLUMN amount INT NOT NULL
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:506B7C12-D36E-4F82-A2A1-5C545CD643CC@.microsoft.com...[vbcol=seagreen]
> What I wanted to do was remove all the non-integer strings first with an
> update to '0' before converting. Do you know what I can do?
> James.
>
> "Adam Machanic" wrote:
have[vbcol=seagreen]
column?[vbcol=seagreen]
as[vbcol=seagreen]
were[vbcol=seagreen]
data[vbcol=seagreen]
select[vbcol=seagreen]
and[vbcol=seagreen]
Showing posts with label char. Show all posts
Showing posts with label char. Show all posts
Friday, March 23, 2012
Script to convert char to int please help....
I ran the following simple select statement :
select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted = '20041123'
Then I got the following error message after several thousand records were
fetched.
Syntax error converting the varchar value 'ZAER "' to a column of data
type int.
What I like to do is convert that column which is char to int or select
everything with the value 'ZAER' and copy to another table and then convert.
I did the first and updated, copied the data back and ran the select and
still got the same error. Can someone help please? Thank you.
JamesHow do you plan to convert the value 'ZAER' to an INT? And why do you have
non-integer strings mixed in with integer amounts in your 'amount' column?
I would recommend that you clean up your data and re-define the column as
INT to avoid these types of issues in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> I ran the following simple select statement :
> select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => '20041123'
> Then I got the following error message after several thousand records were
> fetched.
> Syntax error converting the varchar value 'ZAER "' to a column of data
> type int.
> What I like to do is convert that column which is char to int or select
> everything with the value 'ZAER' and copy to another table and then
convert.
> I did the first and updated, copied the data back and ran the select and
> still got the same error. Can someone help please? Thank you.
> James|||What I wanted to do was remove all the non-integer strings first with an
update to '0' before converting. Do you know what I can do?
James.
"Adam Machanic" wrote:
> How do you plan to convert the value 'ZAER' to an INT? And why do you have
> non-integer strings mixed in with integer amounts in your 'amount' column?
> I would recommend that you clean up your data and re-define the column as
> INT to avoid these types of issues in the future.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > I ran the following simple select statement :
> >
> > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > '20041123'
> > Then I got the following error message after several thousand records were
> > fetched.
> >
> > Syntax error converting the varchar value 'ZAER "' to a column of data
> > type int.
> >
> > What I like to do is convert that column which is char to int or select
> > everything with the value 'ZAER' and copy to another table and then
> convert.
> > I did the first and updated, copied the data back and ran the select and
> > still got the same error. Can someone help please? Thank you.
> >
> > James
>
>|||Here's what I would do:
UPDATE tcase
SET amount= '0'
WHERE PATINDEX('%[^0-9]%', RTRIM(LTRIM(amount))) > 0
OR amount IS NULL
ALTER TABLE tcase
ALTER COLUMN amount INT NOT NULL
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:506B7C12-D36E-4F82-A2A1-5C545CD643CC@.microsoft.com...
> What I wanted to do was remove all the non-integer strings first with an
> update to '0' before converting. Do you know what I can do?
> James.
>
> "Adam Machanic" wrote:
> > How do you plan to convert the value 'ZAER' to an INT? And why do you
have
> > non-integer strings mixed in with integer amounts in your 'amount'
column?
> > I would recommend that you clean up your data and re-define the column
as
> > INT to avoid these types of issues in the future.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> > news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > > I ran the following simple select statement :
> > >
> > > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > > '20041123'
> > > Then I got the following error message after several thousand records
were
> > > fetched.
> > >
> > > Syntax error converting the varchar value 'ZAER "' to a column of
data
> > > type int.
> > >
> > > What I like to do is convert that column which is char to int or
select
> > > everything with the value 'ZAER' and copy to another table and then
> > convert.
> > > I did the first and updated, copied the data back and ran the select
and
> > > still got the same error. Can someone help please? Thank you.
> > >
> > > James
> >
> >
> >
select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted = '20041123'
Then I got the following error message after several thousand records were
fetched.
Syntax error converting the varchar value 'ZAER "' to a column of data
type int.
What I like to do is convert that column which is char to int or select
everything with the value 'ZAER' and copy to another table and then convert.
I did the first and updated, copied the data back and ran the select and
still got the same error. Can someone help please? Thank you.
JamesHow do you plan to convert the value 'ZAER' to an INT? And why do you have
non-integer strings mixed in with integer amounts in your 'amount' column?
I would recommend that you clean up your data and re-define the column as
INT to avoid these types of issues in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> I ran the following simple select statement :
> select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => '20041123'
> Then I got the following error message after several thousand records were
> fetched.
> Syntax error converting the varchar value 'ZAER "' to a column of data
> type int.
> What I like to do is convert that column which is char to int or select
> everything with the value 'ZAER' and copy to another table and then
convert.
> I did the first and updated, copied the data back and ran the select and
> still got the same error. Can someone help please? Thank you.
> James|||What I wanted to do was remove all the non-integer strings first with an
update to '0' before converting. Do you know what I can do?
James.
"Adam Machanic" wrote:
> How do you plan to convert the value 'ZAER' to an INT? And why do you have
> non-integer strings mixed in with integer amounts in your 'amount' column?
> I would recommend that you clean up your data and re-define the column as
> INT to avoid these types of issues in the future.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > I ran the following simple select statement :
> >
> > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > '20041123'
> > Then I got the following error message after several thousand records were
> > fetched.
> >
> > Syntax error converting the varchar value 'ZAER "' to a column of data
> > type int.
> >
> > What I like to do is convert that column which is char to int or select
> > everything with the value 'ZAER' and copy to another table and then
> convert.
> > I did the first and updated, copied the data back and ran the select and
> > still got the same error. Can someone help please? Thank you.
> >
> > James
>
>|||Here's what I would do:
UPDATE tcase
SET amount= '0'
WHERE PATINDEX('%[^0-9]%', RTRIM(LTRIM(amount))) > 0
OR amount IS NULL
ALTER TABLE tcase
ALTER COLUMN amount INT NOT NULL
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:506B7C12-D36E-4F82-A2A1-5C545CD643CC@.microsoft.com...
> What I wanted to do was remove all the non-integer strings first with an
> update to '0' before converting. Do you know what I can do?
> James.
>
> "Adam Machanic" wrote:
> > How do you plan to convert the value 'ZAER' to an INT? And why do you
have
> > non-integer strings mixed in with integer amounts in your 'amount'
column?
> > I would recommend that you clean up your data and re-define the column
as
> > INT to avoid these types of issues in the future.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> > news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > > I ran the following simple select statement :
> > >
> > > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > > '20041123'
> > > Then I got the following error message after several thousand records
were
> > > fetched.
> > >
> > > Syntax error converting the varchar value 'ZAER "' to a column of
data
> > > type int.
> > >
> > > What I like to do is convert that column which is char to int or
select
> > > everything with the value 'ZAER' and copy to another table and then
> > convert.
> > > I did the first and updated, copied the data back and ran the select
and
> > > still got the same error. Can someone help please? Thank you.
> > >
> > > James
> >
> >
> >
Script to change cluster index to non cluster
Hi all,
We have many tables which have cluster index on column with datatype 'Char(200)'.
Does anyone have script to change cluster index to noncluster for all user tables which have clustered index on a column with 'char(200)' datatype.
Thanks,
DeepakDon't know the direct way, but refer to this Devx article (http://www.dev-archive.com/codemag/Article/11516) to script the indexes and modify so on.
HTH|||Here's a link to the SQL Server system tables download:
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp
So, you would do something like this to get all the clustered indexes that includes a CHAR200 col:
SELECT i.name
FROM sysindexes i,
sysobjects o,
syscolumns c
WHERE o.id = i.id
AND o.type = 'U' -- user table
AND indid = 1 -- clustered index
AND o.id = c.id
AND c.type = ??
AND c.length = 200 ?
For c.type look through systypes and find the CHAR, I don't know offhand what the type ID for a char is. And I am not sure if c.length is 200 or what -- take a look in syscolumns and find a couple of the columns you are targeting and see if they have the same data.
We have many tables which have cluster index on column with datatype 'Char(200)'.
Does anyone have script to change cluster index to noncluster for all user tables which have clustered index on a column with 'char(200)' datatype.
Thanks,
DeepakDon't know the direct way, but refer to this Devx article (http://www.dev-archive.com/codemag/Article/11516) to script the indexes and modify so on.
HTH|||Here's a link to the SQL Server system tables download:
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp
So, you would do something like this to get all the clustered indexes that includes a CHAR200 col:
SELECT i.name
FROM sysindexes i,
sysobjects o,
syscolumns c
WHERE o.id = i.id
AND o.type = 'U' -- user table
AND indid = 1 -- clustered index
AND o.id = c.id
AND c.type = ??
AND c.length = 200 ?
For c.type look through systypes and find the CHAR, I don't know offhand what the type ID for a char is. And I am not sure if c.length is 200 or what -- take a look in syscolumns and find a couple of the columns you are targeting and see if they have the same data.
Subscribe to:
Posts (Atom)