Hi,
I have a big database with most of the columns having the datatype char
(char(10), (char(50) etc.)
I would like to change all these columns to varchar (varchar(10),
varchar(50), ...), and have all the spaces on the right trimmed of. So
instead of 'ABC ' I want to have 'ABC'.
Is there a 'nice' way to do this? Changing all the char-columns to varchar
doesn't seem too difficult to me, but would there be a way that automaticly
cuts of this trailing spaces? Or is there some script that exists that does
this for me?
The biggest problem in my opinion is that some of these columns are used as
foreign keys. So I don't know if it is possible that they will be trimmed in
one table, but not yet in the foreign-key-table?
Any help our hints would be really appreciated!
Thanks a lot in advance,
PieterHello, Pieter
To change the data types of the columns, you need to drop the foreign
keys, change all the columns involved (using "ALTER TABLE tbl ALTER
COLUMN col varchar(n)") and then recreate the foreign keys. That's
because a foreign key requires the referencing columns to have the same
data type as the referenced columns (additionally, a column involved in
any kind of constraint cannot be altered).
The trimming can be done before or after recreating the foreign keys,
because when SQL Server compares 'a' with 'a ', they will be equal. For
example, the following would work just fine:
USE tempdb
CREATE TABLE t1 (x varchar(10) primary key)
CREATE TABLE t2 (y varchar(10) references t1)
INSERT INTO t1 VALUES('a')
INSERT INTO t2 VALUES('a ')
SELECT x+'!', y+'!' FROM t1 INNER JOIN t2 ON x=y
DROP TABLE t2,t1
Razvan
Showing posts with label varchar. Show all posts
Showing posts with label varchar. Show all posts
Tuesday, March 27, 2012
Change from char to varchar and RTRIM
Hi,
I have a big database with most of the columns having the datatype char
(char(10), (char(50) etc.)
I would like to change all these columns to varchar (varchar(10),
varchar(50), ...), and have all the spaces on the right trimmed of. So
instead of 'ABC ' I want to have 'ABC'.
Is there a 'nice' way to do this? Changing all the char-columns to varchar
doesn't seem too difficult to me, but would there be a way that automaticly
cuts of this trailing spaces? Or is there some script that exists that does
this for me?
The biggest problem in my opinion is that some of these columns are used as
foreign keys. So I don't know if it is possible that they will be trimmed in
one table, but not yet in the foreign-key-table?
Any help our hints would be really appreciated!
Thanks a lot in advance,
PieterHello, Pieter
To change the data types of the columns, you need to drop the foreign
keys, change all the columns involved (using "ALTER TABLE tbl ALTER
COLUMN col varchar(n)") and then recreate the foreign keys. That's
because a foreign key requires the referencing columns to have the same
data type as the referenced columns (additionally, a column involved in
any kind of constraint cannot be altered).
The trimming can be done before or after recreating the foreign keys,
because when SQL Server compares 'a' with 'a ', they will be equal. For
example, the following would work just fine:
USE tempdb
CREATE TABLE t1 (x varchar(10) primary key)
CREATE TABLE t2 (y varchar(10) references t1)
INSERT INTO t1 VALUES('a')
INSERT INTO t2 VALUES('a ')
SELECT x+'!', y+'!' FROM t1 INNER JOIN t2 ON x=y
DROP TABLE t2,t1
Razvan
I have a big database with most of the columns having the datatype char
(char(10), (char(50) etc.)
I would like to change all these columns to varchar (varchar(10),
varchar(50), ...), and have all the spaces on the right trimmed of. So
instead of 'ABC ' I want to have 'ABC'.
Is there a 'nice' way to do this? Changing all the char-columns to varchar
doesn't seem too difficult to me, but would there be a way that automaticly
cuts of this trailing spaces? Or is there some script that exists that does
this for me?
The biggest problem in my opinion is that some of these columns are used as
foreign keys. So I don't know if it is possible that they will be trimmed in
one table, but not yet in the foreign-key-table?
Any help our hints would be really appreciated!
Thanks a lot in advance,
PieterHello, Pieter
To change the data types of the columns, you need to drop the foreign
keys, change all the columns involved (using "ALTER TABLE tbl ALTER
COLUMN col varchar(n)") and then recreate the foreign keys. That's
because a foreign key requires the referencing columns to have the same
data type as the referenced columns (additionally, a column involved in
any kind of constraint cannot be altered).
The trimming can be done before or after recreating the foreign keys,
because when SQL Server compares 'a' with 'a ', they will be equal. For
example, the following would work just fine:
USE tempdb
CREATE TABLE t1 (x varchar(10) primary key)
CREATE TABLE t2 (y varchar(10) references t1)
INSERT INTO t1 VALUES('a')
INSERT INTO t2 VALUES('a ')
SELECT x+'!', y+'!' FROM t1 INNER JOIN t2 ON x=y
DROP TABLE t2,t1
Razvan
Tuesday, March 20, 2012
change datatype of a column
what is the best (and fast) why to change a column's datatype from varchar to decimal ?
the table has 3 million records and the column is filled with data (no problem with converting the data to numeric).alter table tablename
alter column columnname float null|||tnx
the table has 3 million records and the column is filled with data (no problem with converting the data to numeric).alter table tablename
alter column columnname float null|||tnx
Change datatype from varchar to bigint not working
Hello,
I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.
I have the command ready which is:
ALTER TABLE tablename ALTER COLUMN columnname BIGINT
The problem happening is that it seems there are constraints across all the columns in every tables.
The error message is:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.
I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.Hi
This should help you:
http://www.sqlteam.com/article/default-constraint-names
I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.
I have the command ready which is:
ALTER TABLE tablename ALTER COLUMN columnname BIGINT
The problem happening is that it seems there are constraints across all the columns in every tables.
The error message is:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.
I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.Hi
This should help you:
http://www.sqlteam.com/article/default-constraint-names
Sunday, March 11, 2012
Change condition if the first doesn't exists
Have two tables:
What I would like is that if LingoId = 2 in the query below doesn't exists
than it should fall thru and use the values where LingoId = 1. LingoId = 1
always exists for each Table2.Id. Is this doable?
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Header ELSE
T2_1.Header END) as Header,
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Description ELSE
T2_1.Description END) as Description
FROM
dbo.Table1
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 2) T2_2
ON dbo.Table1.Id = T2_2.Table1Id
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 1) T2_1
ON dbo.Table1.Id = T2_1.Table1Id
HTH, Jens Suessmeyer.|||Got an answer elsewhere that did the trick.
"Senna" wrote:
>
Have two tables:
>
>
>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>
effectively be an INNER JOIN. Did you mean it like that?
If you want to use only one query then something like this could be good
enough:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = case when exists (select table1.id from table1
inner join table2 on table1.id = table2.id where table2.LingoID = 2) then 2
else 1 end)
MC
"Senna" <
Senna@.discussions.microsoft.com>
wrote in message
news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
Have two tables:
>
>
>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
INNER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
dbo.Table2.LingoId = 2
or (dbo.Table2.LingoId = 1
and not exists
(select *
from table2 cn
where cn.table1id = dbo.Table2.Table1Id
and cn.lingoid = 2))
ps. Thank for your time and answer Jens.
"Senna" wrote:
>
Have two tables:
>
>
>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>
See the other post with the solution I went with. Thanks anyway for your
time and effort.
"MC" wrote:
> First, you are filtering the result with values from table2. That will
> effectively be an INNER JOIN. Did you mean it like that?
> If you want to use only one query then something like this could be good
> enough:
> SELECT
> dbo.Table1.Id,
> dbo.Table1.Number,
> dbo.Table1.[TimeStamp],
> dbo.Table2.Header,
> dbo.Table2.Description
> FROM
> dbo.Table1
> LEFT OUTER JOIN
> dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
> WHERE
> (dbo.Table2.LingoId = case when exists (select table1.id from table1
> inner join table2 on table1.id = table2.id where table2.LingoID = 2) then
2
> else 1 end)
>
> MC
>
> "Senna" <Senna@.discussions.microsoft.com> wrote in message
> news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
>
code:
CREATE TABLE [Table1]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Number] [varchar] (50) NOT NULL ,
[TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
DEFAULT (getdate()),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table2]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Table1Id] [int] NOT NULL ,
[LingoId] [int] NOT NULL ,
[Header] [nvarchar] (150) NOT NULL ,
[Description] [ntext] NOT NULL ,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
(
[Table1Id]
) REFERENCES [Table1] (
[Id]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
What I would like is that if LingoId = 2 in the query below doesn't exists
than it should fall thru and use the values where LingoId = 1. LingoId = 1
always exists for each Table2.Id. Is this doable?
SELECTcode:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = 2) --Something should happen here I quess.
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Header ELSE
T2_1.Header END) as Header,
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Description ELSE
T2_1.Description END) as Description
FROM
dbo.Table1
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 2) T2_2
ON dbo.Table1.Id = T2_2.Table1Id
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 1) T2_1
ON dbo.Table1.Id = T2_1.Table1Id
HTH, Jens Suessmeyer.|||Got an answer elsewhere that did the trick.
"Senna" wrote:
>
Have two tables:
>
>
code:
>
CREATE TABLE [Table1]
>
(
>
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
[Number] [varchar] (50) NOT NULL ,
>
[TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
DEFAULT (getdate()),
>
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
(
>
[Id]
>
) ON [PRIMARY]
>
) ON [PRIMARY]
>
GO
>
>
CREATE TABLE [Table2]
>
(
>
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
[Table1Id] [int] NOT NULL ,
>
[LingoId] [int] NOT NULL ,
>
[Header] [nvarchar] (150) NOT NULL ,
>
[Description] [ntext] NOT NULL ,
>
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
(
>
[Id]
>
) ON [PRIMARY] ,
>
CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
(
>
[Table1Id]
>
) REFERENCES [Table1] (
>
[Id]
>
)
>
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
GO
>
>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>
|||First, you are filtering the result with values from table2. That willcode:
>
SELECT
>
dbo.Table1.Id,
>
dbo.Table1.Number,
>
dbo.Table1.[TimeStamp],
>
dbo.Table2.Header,
>
dbo.Table2.Description
>
FROM
>
dbo.Table1
>
LEFT OUTER JOIN
>
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
WHERE
>
(dbo.Table2.LingoId = 2) --Something should happen here I quess.
>
effectively be an INNER JOIN. Did you mean it like that?
If you want to use only one query then something like this could be good
enough:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = case when exists (select table1.id from table1
inner join table2 on table1.id = table2.id where table2.LingoID = 2) then 2
else 1 end)
MC
"Senna" <
Senna@.discussions.microsoft.com>
wrote in message
news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
Have two tables:
>
>
code:
>
CREATE TABLE [Table1]
>
(
>
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
[Number] [varchar] (50) NOT NULL ,
>
[TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
DEFAULT (getdate()),
>
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
(
>
[Id]
>
) ON [PRIMARY]
>
) ON [PRIMARY]
>
GO
>
>
CREATE TABLE [Table2]
>
(
>
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
[Table1Id] [int] NOT NULL ,
>
[LingoId] [int] NOT NULL ,
>
[Header] [nvarchar] (150) NOT NULL ,
>
[Description] [ntext] NOT NULL ,
>
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
(
>
[Id]
>
) ON [PRIMARY] ,
>
CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
(
>
[Table1Id]
>
) REFERENCES [Table1] (
>
[Id]
>
)
>
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
GO
>
>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>
|||The solution, as I mention above, looked liked this:code:
>
SELECT
>
dbo.Table1.Id,
>
dbo.Table1.Number,
>
dbo.Table1.[TimeStamp],
>
dbo.Table2.Header,
>
dbo.Table2.Description
>
FROM
>
dbo.Table1
>
LEFT OUTER JOIN
>
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
WHERE
>
(dbo.Table2.LingoId = 2) --Something should happen here I quess.
>
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
INNER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
dbo.Table2.LingoId = 2
or (dbo.Table2.LingoId = 1
and not exists
(select *
from table2 cn
where cn.table1id = dbo.Table2.Table1Id
and cn.lingoid = 2))
ps. Thank for your time and answer Jens.
"Senna" wrote:
>
Have two tables:
>
>
code:
>
CREATE TABLE [Table1]
>
(
>
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
[Number] [varchar] (50) NOT NULL ,
>
[TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
DEFAULT (getdate()),
>
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
(
>
[Id]
>
) ON [PRIMARY]
>
) ON [PRIMARY]
>
GO
>
>
CREATE TABLE [Table2]
>
(
>
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
[Table1Id] [int] NOT NULL ,
>
[LingoId] [int] NOT NULL ,
>
[Header] [nvarchar] (150) NOT NULL ,
>
[Description] [ntext] NOT NULL ,
>
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
(
>
[Id]
>
) ON [PRIMARY] ,
>
CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
(
>
[Table1Id]
>
) REFERENCES [Table1] (
>
[Id]
>
)
>
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
GO
>
>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>
|||Yes, it was an inner join. :)code:
>
SELECT
>
dbo.Table1.Id,
>
dbo.Table1.Number,
>
dbo.Table1.[TimeStamp],
>
dbo.Table2.Header,
>
dbo.Table2.Description
>
FROM
>
dbo.Table1
>
LEFT OUTER JOIN
>
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
WHERE
>
(dbo.Table2.LingoId = 2) --Something should happen here I quess.
>
See the other post with the solution I went with. Thanks anyway for your
time and effort.
"MC" wrote:
> First, you are filtering the result with values from table2. That will
> effectively be an INNER JOIN. Did you mean it like that?
> If you want to use only one query then something like this could be good
> enough:
> SELECT
> dbo.Table1.Id,
> dbo.Table1.Number,
> dbo.Table1.[TimeStamp],
> dbo.Table2.Header,
> dbo.Table2.Description
> FROM
> dbo.Table1
> LEFT OUTER JOIN
> dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
> WHERE
> (dbo.Table2.LingoId = case when exists (select table1.id from table1
> inner join table2 on table1.id = table2.id where table2.LingoID = 2) then
2
> else 1 end)
>
> MC
>
> "Senna" <Senna@.discussions.microsoft.com> wrote in message
> news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
>
change column width
Hi there,
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, CalinTester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar
80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NUL
L
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, CalinTester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar
80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NUL
L
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
change column width
Hi there,
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, Calin
Tester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar 80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, Calin
Tester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar 80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
change column width
Hi there,
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, CalinTester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar 80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, CalinTester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar 80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com
Thursday, March 8, 2012
change all char columns to varchar
Hi ,
Our company decided to convert all char columns to varchar in our next
software release.
We have lot (40+) of client databases which is more than 75GB .
Below the query I wrote for this ,but it's taking long long hours to
complete the upgrade.
Is there any better way to write this '
Thanks
--*******************************************************************
-- Script to convert all char columns to varchar
--*******************************************************************
declare @.tbname varchar(255)
declare @.column_name varchar(255)
declare @.is_nullable varchar(255)
declare @.character_maximum_length varchar(255)
declare @.Query varchar(8000)
declare c1 cursor for
select
collist.table_name,collist.column_name,collist.is_nullable,collist.character
_maximum_length from information_schema.columns
collist,information_schema.tables tablist
where collist.table_name=tablist.table_name and tablist.table_type='BASE
TABLE' and data_type='char'
open c1
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
while @.@.fetch_status=0
begin
if (@.is_nullable='No')
set @.is_nullable='NOT NULL'
else
set @.is_nullable='NULL'
set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
VARCHAR('+@.character_maximum_length+') '+@.is_nullable
exec (@.Query)
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
end
deallocate c1
GO
PRINT '** Script 07 -- 07.char to varchar.sql completed **'
goI think you can change the Cursor to table variable ... Add Set NOCOUNT ON
to remove the un-necessary verbose of "n rows affected" ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.character
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>|||Each ALTER TABLE statement will require updating every row in the table.
This will take quite some time if you have many char columns in a single
table because you'll be updating each row many times. Furthermore, this
will not trim trailing spaces from the char column and leave a lot of
other wasted space in the table.
You will be better off creating a set of tables with the new structure
and loading via DTS. You can trim spaces during the process. This
method will provide the additional flexibility of allowing you to keep
existing char datatypes where appropriate.
Another method is to create new tables using SELECT INTO. This can be a
bit tricky and is problematic with identity columns. Example below.
CREATE TABLE MyTable
(
Col1 int NOT NULL,
Col2 char(10) NOT NULL,
Col3 char(10) NULL
)
INSERT INTO MyTable VALUES (1, 'a', 'b')
GO
SELECT
Col1,
-- need to specify ISNULL to create a NOT NULL
ISNULL(CAST(RTRIM(Col2) AS varchar(10)), '') AS Col2,
CAST(RTRIM(Col2) AS varchar(10)) AS Col3
INTO MyTable_New
FROM MyTable
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.chara
cter
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and
tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>
Our company decided to convert all char columns to varchar in our next
software release.
We have lot (40+) of client databases which is more than 75GB .
Below the query I wrote for this ,but it's taking long long hours to
complete the upgrade.
Is there any better way to write this '
Thanks
--*******************************************************************
-- Script to convert all char columns to varchar
--*******************************************************************
declare @.tbname varchar(255)
declare @.column_name varchar(255)
declare @.is_nullable varchar(255)
declare @.character_maximum_length varchar(255)
declare @.Query varchar(8000)
declare c1 cursor for
select
collist.table_name,collist.column_name,collist.is_nullable,collist.character
_maximum_length from information_schema.columns
collist,information_schema.tables tablist
where collist.table_name=tablist.table_name and tablist.table_type='BASE
TABLE' and data_type='char'
open c1
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
while @.@.fetch_status=0
begin
if (@.is_nullable='No')
set @.is_nullable='NOT NULL'
else
set @.is_nullable='NULL'
set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
VARCHAR('+@.character_maximum_length+') '+@.is_nullable
exec (@.Query)
fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
end
deallocate c1
GO
PRINT '** Script 07 -- 07.char to varchar.sql completed **'
goI think you can change the Cursor to table variable ... Add Set NOCOUNT ON
to remove the un-necessary verbose of "n rows affected" ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.character
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into @.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>|||Each ALTER TABLE statement will require updating every row in the table.
This will take quite some time if you have many char columns in a single
table because you'll be updating each row many times. Furthermore, this
will not trim trailing spaces from the char column and leave a lot of
other wasted space in the table.
You will be better off creating a set of tables with the new structure
and loading via DTS. You can trim spaces during the process. This
method will provide the additional flexibility of allowing you to keep
existing char datatypes where appropriate.
Another method is to create new tables using SELECT INTO. This can be a
bit tricky and is problematic with identity columns. Example below.
CREATE TABLE MyTable
(
Col1 int NOT NULL,
Col2 char(10) NOT NULL,
Col3 char(10) NULL
)
INSERT INTO MyTable VALUES (1, 'a', 'b')
GO
SELECT
Col1,
-- need to specify ISNULL to create a NOT NULL
ISNULL(CAST(RTRIM(Col2) AS varchar(10)), '') AS Col2,
CAST(RTRIM(Col2) AS varchar(10)) AS Col3
INTO MyTable_New
FROM MyTable
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uQ78i$QmDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> Our company decided to convert all char columns to varchar in our next
> software release.
> We have lot (40+) of client databases which is more than 75GB .
> Below the query I wrote for this ,but it's taking long long hours to
> complete the upgrade.
> Is there any better way to write this '
> Thanks
>
> --*******************************************************************
> -- Script to convert all char columns to varchar
> --*******************************************************************
> declare @.tbname varchar(255)
> declare @.column_name varchar(255)
> declare @.is_nullable varchar(255)
> declare @.character_maximum_length varchar(255)
> declare @.Query varchar(8000)
> declare c1 cursor for
> select
>
collist.table_name,collist.column_name,collist.is_nullable,collist.chara
cter
> _maximum_length from information_schema.columns
> collist,information_schema.tables tablist
> where collist.table_name=tablist.table_name and
tablist.table_type='BASE
> TABLE' and data_type='char'
> open c1
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> while @.@.fetch_status=0
> begin
> if (@.is_nullable='No')
> set @.is_nullable='NOT NULL'
> else
> set @.is_nullable='NULL'
> set @.Query='alter table '+@.tbname +' alter column '+@.column_name+'
> VARCHAR('+@.character_maximum_length+') '+@.is_nullable
> exec (@.Query)
> fetch c1 into
@.tbname,@.column_name,@.is_nullable,@.character_maximum_length
> end
> deallocate c1
> GO
> PRINT '** Script 07 -- 07.char to varchar.sql completed **'
> go
>
>
Wednesday, March 7, 2012
challenge...
How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)?
Like I have a table employee
Employee
(
colA int
colB int
colC varchar
)
If i run the tsql..
it should give me
Employee
(
colA varchar
colB varchar
colC varchar
)
Is the table empty? If not, what is the disposition of the data that is already in the table?
|||Well you could try using a change script generated by Enterprise Manager, like this one:
Code Snippet
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Employee
(
ColA varchar(1000) NULL,
ColB varchar(1000) NULL,
ColC varchar(1000) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Employee)
EXEC('INSERT INTO dbo.Tmp_Employee (ColA, ColB, ColC)
SELECT CONVERT(varchar(1000), ColA), CONVERT(varchar(1000), ColB), ColC FROM dbo.Employee (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'Employee', 'OBJECT'
GO
COMMIT
Please don't use the graphical tools to make schema changes. It can generate scripts that are inefficient and unnecessary. You can just use ALTER TABLE to change the column from int to varchar in this case. If you do use the tools then please make sure to review the scripts because there is lot of things that can be simplified or improved. For example, in above case there is no reason to do CREATE TABLE and INSERT. You can do SELECT...INTO - this can perform minimally logged operations & can run magnitudes of time faster.
Sunday, February 19, 2012
CDONS.mail not working
I have a procedure that sends mail using CDONTS.mail
CREATE PROCEDURE SendMail(
@.From varchar(255),
@.To varchar(255),
@.Message varchar(8000),
@.Subject varchar(255))
AS
DECLARE @.CDO int, @.OLEResult int, @.Out int
--Create CDONTS.NewMail object
EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
--Call Send method of the object
execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
@.Subject, @.Message, 1 --0 is low 1 is normal
IF @.OLEResult <> 0 PRINT 'Send'
--Destroy CDO
EXECUTE @.OLEResult = sp_OADestroy @.CDO
return @.OLEResult
It runs well in a server but fails in other server
When I executed it in QA, it displays "The command(s) completed
successfully." without sending the mail
Is that server missing any settings?
MadhivananDon't use CDONTS.NewMail, it's been deprecated and no longer ships with
Windows.
My suggestion is to set up an SMTP server and use xp_smtp_sendmail. Barring
that, use CDO.Message. See http://www.aspfaq.com/2403 for more details on
both methods (note that in the working sample, each property is set
individually, whereas you attempt to pass a bunch of properties into a
single call).
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1136812148.799884.141170@.g49g2000cwa.googlegroups.com...
> I have a procedure that sends mail using CDONTS.mail
>
> CREATE PROCEDURE SendMail(
> @.From varchar(255),
> @.To varchar(255),
> @.Message varchar(8000),
> @.Subject varchar(255))
> AS
> DECLARE @.CDO int, @.OLEResult int, @.Out int
> --Create CDONTS.NewMail object
> EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
> IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
>
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
> --Call Send method of the object
> execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
> @.Subject, @.Message, 1 --0 is low 1 is normal
> IF @.OLEResult <> 0 PRINT 'Send'
> --Destroy CDO
> EXECUTE @.OLEResult = sp_OADestroy @.CDO
> return @.OLEResult
> It runs well in a server but fails in other server
> When I executed it in QA, it displays "The command(s) completed
> successfully." without sending the mail
> Is that server missing any settings?
> Madhivanan
>
CREATE PROCEDURE SendMail(
@.From varchar(255),
@.To varchar(255),
@.Message varchar(8000),
@.Subject varchar(255))
AS
DECLARE @.CDO int, @.OLEResult int, @.Out int
--Create CDONTS.NewMail object
EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
--Call Send method of the object
execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
@.Subject, @.Message, 1 --0 is low 1 is normal
IF @.OLEResult <> 0 PRINT 'Send'
--Destroy CDO
EXECUTE @.OLEResult = sp_OADestroy @.CDO
return @.OLEResult
It runs well in a server but fails in other server
When I executed it in QA, it displays "The command(s) completed
successfully." without sending the mail
Is that server missing any settings?
MadhivananDon't use CDONTS.NewMail, it's been deprecated and no longer ships with
Windows.
My suggestion is to set up an SMTP server and use xp_smtp_sendmail. Barring
that, use CDO.Message. See http://www.aspfaq.com/2403 for more details on
both methods (note that in the working sample, each property is set
individually, whereas you attempt to pass a bunch of properties into a
single call).
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1136812148.799884.141170@.g49g2000cwa.googlegroups.com...
> I have a procedure that sends mail using CDONTS.mail
>
> CREATE PROCEDURE SendMail(
> @.From varchar(255),
> @.To varchar(255),
> @.Message varchar(8000),
> @.Subject varchar(255))
> AS
> DECLARE @.CDO int, @.OLEResult int, @.Out int
> --Create CDONTS.NewMail object
> EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
> IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
>
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
> --Call Send method of the object
> execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
> @.Subject, @.Message, 1 --0 is low 1 is normal
> IF @.OLEResult <> 0 PRINT 'Send'
> --Destroy CDO
> EXECUTE @.OLEResult = sp_OADestroy @.CDO
> return @.OLEResult
> It runs well in a server but fails in other server
> When I executed it in QA, it displays "The command(s) completed
> successfully." without sending the mail
> Is that server missing any settings?
> Madhivanan
>
CDBVariant
Hi
got small problem. I got three rows in a table
Index (int)
Product (varchar)
Price (float)
I could successfully connect to the database. I also get the values and store them in a CDBVariant type. I can't put the values into List Control Box... insertItem always return -1. It ask for LPCTSTR type.
Anyone can help me please, hanging around here know for hours?
Please
Thx
CODE
{
CDBVariant value;
char sql_statement [2048] = "";
//CDatabase object "db" created to connect database
CDatabase db;
db.OpenEx(_T("DSN=Beauty"),CDatabase::noOdbcDialog);
//CRecordset object "rs" created to access and manipulate database records.
CRecordset rs(&db);
strcpy(sql_statement,_T("SELECT * FROM TestTabelle"));
rs.Open(CRecordset::forwardOnly,sql_statement);
//Get quantity from Database
int n = rs.GetODBCFieldCount( );
while(!rs.IsEOF())
{
for( int i = 0; i < n; i++ )
{
rs.GetFieldValue("index",value);
m_Buy_List.InsertItem(i,LPCTSTR(value.m_lVal)); Won't work at all
rs.GetFieldValue("product",value.m_pstring);
m_Buy_List.SetItemText(i,2,LPCTSTR(value));Getting weird String for example "Soap" and ListBox shows "Aq3"
rs.GetFieldValue("price",value);
m_Buy_List.SetItemText(i,3,LPCTSTR(value.m_fltVal) ); Won't accept float
rs.MoveNext( );
}
}Perhaps you would get better results on a C++ forum...
If you know that the values must be strings, and you are retrieving each one explicitly, why not just create three variables (strings), and use those. Seems like it would be a lot easier and less trouble prone.
If you are concerned about NULLs (which you should be), change your SQL statement to use the ISNULL function on each of the fields you are dealing with:
SELECT ISNULL(index,0) AS INDEX, ISNULL(product,'') AS PRODUCT, ISNULL(price,0) AS PRICE FROM TestTabelle|||thx
i figured another way out... getFieldValue isn't accepting strings so i converted them in char :)
weird... someday i will find the official way... i will change it then :)
iam new into c++ and ms sql... learning by doing :)
got small problem. I got three rows in a table
Index (int)
Product (varchar)
Price (float)
I could successfully connect to the database. I also get the values and store them in a CDBVariant type. I can't put the values into List Control Box... insertItem always return -1. It ask for LPCTSTR type.
Anyone can help me please, hanging around here know for hours?
Please
Thx
CODE
{
CDBVariant value;
char sql_statement [2048] = "";
//CDatabase object "db" created to connect database
CDatabase db;
db.OpenEx(_T("DSN=Beauty"),CDatabase::noOdbcDialog);
//CRecordset object "rs" created to access and manipulate database records.
CRecordset rs(&db);
strcpy(sql_statement,_T("SELECT * FROM TestTabelle"));
rs.Open(CRecordset::forwardOnly,sql_statement);
//Get quantity from Database
int n = rs.GetODBCFieldCount( );
while(!rs.IsEOF())
{
for( int i = 0; i < n; i++ )
{
rs.GetFieldValue("index",value);
m_Buy_List.InsertItem(i,LPCTSTR(value.m_lVal)); Won't work at all
rs.GetFieldValue("product",value.m_pstring);
m_Buy_List.SetItemText(i,2,LPCTSTR(value));Getting weird String for example "Soap" and ListBox shows "Aq3"
rs.GetFieldValue("price",value);
m_Buy_List.SetItemText(i,3,LPCTSTR(value.m_fltVal) ); Won't accept float
rs.MoveNext( );
}
}Perhaps you would get better results on a C++ forum...
If you know that the values must be strings, and you are retrieving each one explicitly, why not just create three variables (strings), and use those. Seems like it would be a lot easier and less trouble prone.
If you are concerned about NULLs (which you should be), change your SQL statement to use the ISNULL function on each of the fields you are dealing with:
SELECT ISNULL(index,0) AS INDEX, ISNULL(product,'') AS PRODUCT, ISNULL(price,0) AS PRICE FROM TestTabelle|||thx
i figured another way out... getFieldValue isn't accepting strings so i converted them in char :)
weird... someday i will find the official way... i will change it then :)
iam new into c++ and ms sql... learning by doing :)
Tuesday, February 14, 2012
Catalog and characters with accent
I have a question about SQL Server 2000 Full Text Index.
I want to create a catalog in a field (varchar(255)), but Im with 2
problems:
1. The characters of this field can have accent. But when I do a search
I want to see the rows with and without the accent. For example:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"PLASTICO*"')
With this command I want to see the row PLASTICO and the row PLSTICO.
Is it possible? Now, Im just receiving only the row PLASTICO. I need
that the catalog be accent insensitive. Can I do that?
2. My sencond problem is: I need to see also the rows that have the word
PLASTICO inside the complete word. For example: I want to see also the
rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
following command I dont receive these words:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"*PLASTICO*"')
Is it possible to do that? I wnat to see the rows that have the word
PLASTIC in the begin, middle or end of the words.
Thaks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***
SQL 2005 can solve both your problems. You can configure your catalog for
accent insensitive searches. You can also use the thesaurus option to expand
your search on plastico to search on interpastico, 2plastico, and
xptoplastico, as long as you enter all of these expansion terms into your
thesaurus file in advance.
In SQL 2000 you have to expand your search terms for accented or unaccented
versions as well as the alternate word forms.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paulo Andre Ortega Ribeiro" <paulo.andre.66@.terra.com.br> wrote in message
news:O%23k7qFyeFHA.2740@.TK2MSFTNGP10.phx.gbl...
> I have a question about SQL Server 2000 Full Text Index.
> I want to create a catalog in a field (varchar(255)), but Im with 2
> problems:
> 1. The characters of this field can have accent. But when I do a search
> I want to see the rows with and without the accent. For example:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"PLASTICO*"')
> With this command I want to see the row PLASTICO and the row PLSTICO.
> Is it possible? Now, Im just receiving only the row PLASTICO. I need
> that the catalog be accent insensitive. Can I do that?
> 2. My sencond problem is: I need to see also the rows that have the word
> PLASTICO inside the complete word. For example: I want to see also the
> rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
> following command I dont receive these words:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"*PLASTICO*"')
> Is it possible to do that? I wnat to see the rows that have the word
> PLASTIC in the begin, middle or end of the words.
> Thaks,
> Paulo
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
I want to create a catalog in a field (varchar(255)), but Im with 2
problems:
1. The characters of this field can have accent. But when I do a search
I want to see the rows with and without the accent. For example:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"PLASTICO*"')
With this command I want to see the row PLASTICO and the row PLSTICO.
Is it possible? Now, Im just receiving only the row PLASTICO. I need
that the catalog be accent insensitive. Can I do that?
2. My sencond problem is: I need to see also the rows that have the word
PLASTICO inside the complete word. For example: I want to see also the
rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
following command I dont receive these words:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"*PLASTICO*"')
Is it possible to do that? I wnat to see the rows that have the word
PLASTIC in the begin, middle or end of the words.
Thaks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***
SQL 2005 can solve both your problems. You can configure your catalog for
accent insensitive searches. You can also use the thesaurus option to expand
your search on plastico to search on interpastico, 2plastico, and
xptoplastico, as long as you enter all of these expansion terms into your
thesaurus file in advance.
In SQL 2000 you have to expand your search terms for accented or unaccented
versions as well as the alternate word forms.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paulo Andre Ortega Ribeiro" <paulo.andre.66@.terra.com.br> wrote in message
news:O%23k7qFyeFHA.2740@.TK2MSFTNGP10.phx.gbl...
> I have a question about SQL Server 2000 Full Text Index.
> I want to create a catalog in a field (varchar(255)), but Im with 2
> problems:
> 1. The characters of this field can have accent. But when I do a search
> I want to see the rows with and without the accent. For example:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"PLASTICO*"')
> With this command I want to see the row PLASTICO and the row PLSTICO.
> Is it possible? Now, Im just receiving only the row PLASTICO. I need
> that the catalog be accent insensitive. Can I do that?
> 2. My sencond problem is: I need to see also the rows that have the word
> PLASTICO inside the complete word. For example: I want to see also the
> rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
> following command I dont receive these words:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"*PLASTICO*"')
> Is it possible to do that? I wnat to see the rows that have the word
> PLASTIC in the begin, middle or end of the words.
> Thaks,
> Paulo
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
Sunday, February 12, 2012
Casting question
HI all,
Quick question about a trigger i am developing.
I need to take a varchar string variable and convert it and store it in an
integer variable.
How do i write that statement.
Pls keep in mind that this is inside a trigger not inside a SQL statement.
Thanks in advance,
Colin
csmart@.nf.sympatico.caassuming the value is a number value an inplicit conversion will occur
take a look at this
declare @.v varchar(50),@.i int
select @.v ='1212121'
select @.i =@.v -- implicit conversion
select convert(int,@.v),@.i
the only problem you will have is if the value is bigger than an int
can hold or not a number
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||try isnumeric function to check if its a valid numeric
Quick question about a trigger i am developing.
I need to take a varchar string variable and convert it and store it in an
integer variable.
How do i write that statement.
Pls keep in mind that this is inside a trigger not inside a SQL statement.
Thanks in advance,
Colin
csmart@.nf.sympatico.caassuming the value is a number value an inplicit conversion will occur
take a look at this
declare @.v varchar(50),@.i int
select @.v ='1212121'
select @.i =@.v -- implicit conversion
select convert(int,@.v),@.i
the only problem you will have is if the value is bigger than an int
can hold or not a number
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||try isnumeric function to check if its a valid numeric
Casting Int to Varchar
I want to cast an In to varchar with a specific number of decimal places. So 10 will come across as 10.00.
Is there an easy way of doing this?Would you be llooking for something like this?
Code:
------------------------------
select cast(cast(10 as money) as varchar(10))
select cast(cast(10 as numeric(12,4)) as varchar(10))
------------------------------|||I think this is what Paul was trying to show:
select cast(cast(id as decimal(5,2)) as varchar(10)) from table
Where id and table are defined by you. The decimal parameters would be determined by your maximum integer.
Is there an easy way of doing this?Would you be llooking for something like this?
Code:
------------------------------
select cast(cast(10 as money) as varchar(10))
select cast(cast(10 as numeric(12,4)) as varchar(10))
------------------------------|||I think this is what Paul was trying to show:
select cast(cast(id as decimal(5,2)) as varchar(10)) from table
Where id and table are defined by you. The decimal parameters would be determined by your maximum integer.
CAST/Convert and performance measurements
Has anyone any testdata on how long a cast/convert from varchar to other datatypes is taking. Is casting a large number of data a major problem for MSSQL 2000?This is what I know.
CAST is the old way that SQL used to change from one data type to another. CONVERT is the preferred method according Microsoft.
I've used CONVERT on tables with a couple hundred thousand rows and didn't really see a large degradation in performance.
Hope this helps.|||CAST has simpler syntax, CONVERT has more functionality. They both work fast, and if you look at the documentation for them you'll see that SQL Server will do many conversion implicitily, so that you don't even need to use CAST or CONVERT.
blindman
CAST is the old way that SQL used to change from one data type to another. CONVERT is the preferred method according Microsoft.
I've used CONVERT on tables with a couple hundred thousand rows and didn't really see a large degradation in performance.
Hope this helps.|||CAST has simpler syntax, CONVERT has more functionality. They both work fast, and if you look at the documentation for them you'll see that SQL Server will do many conversion implicitily, so that you don't even need to use CAST or CONVERT.
blindman
Cast varchar to decimal
I am losing my hair...and my mind...
Is there any reason why I wouldn't be able to cast a varchar value of say
7.8 to decimal?
I have a whole bunch of lab results that come with a bunch of garbage in the
result column. I have stripped it away so that it is only a format
[1-x].[0-9]. I want to make it a number so I can identify High and low value
s
for each patient.
Am I missing something...besides my mind?
Thanks in advance,Don't see any problem in achieving what you want.
Can you post a sample to exactly understand what your issue is?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:CC2448DB-9147-4391-BBDA-7891A6E3294F@.microsoft.com...
>I am losing my hair...and my mind...
> Is there any reason why I wouldn't be able to cast a varchar value of say
> 7.8 to decimal?
> I have a whole bunch of lab results that come with a bunch of garbage in
> the
> result column. I have stripped it away so that it is only a format
> [1-x].[0-9]. I want to make it a number so I can identify High and low
> values
> for each patient.
> Am I missing something...besides my mind?
> Thanks in advance,
>
>|||Here is the code... pretty straight forward....
There is something still contained in the string that is messing up the
cast. (See raw data below code)
select top 7
patientid,
decodedvalue,
convert(varchar,replace(replace(replace(
decodedvalue,'
',''),'>',''),'%','')) value,
-- cast(convert(varchar,replace(replace(r
eplace(decodedvalue,'
',''),'>',''),'%','')) as float) value,
len(replace(replace(replace(decodedvalue
,' ',''),'>',''),'%','')) str_length
from
#diabetes_results
where
decodedvalue like '%.%'
order by
patientid
58 6.8 % 6.8 3
58 7.6 % 7.6 3
58 6.7 % 6.7 3
58 7.1 % 7.1 3
58 6.2 % 6.2 3
168 7.5 % 7.5 3
168 7.5 7.5 5
Note the length of '5' in the final record though it is obvious the length
should be 3. I trimmed the column of spaces but they remain.
Any thoughts?
Thanks in advance,
"SriSamp" wrote:
> Don't see any problem in achieving what you want.
> Can you post a sample to exactly understand what your issue is?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:CC2448DB-9147-4391-BBDA-7891A6E3294F@.microsoft.com...
>
>|||168 7.5 7.5 5
Perhaps those aren't spaces - in fact I think they are one carriage return
and one line feed character. How are these values inserted? You should
prevent illegal values from being entered at all.
In the mean time - remove char(13) and char(10) from the string.
ML
http://milambda.blogspot.com/
Is there any reason why I wouldn't be able to cast a varchar value of say
7.8 to decimal?
I have a whole bunch of lab results that come with a bunch of garbage in the
result column. I have stripped it away so that it is only a format
[1-x].[0-9]. I want to make it a number so I can identify High and low value
s
for each patient.
Am I missing something...besides my mind?
Thanks in advance,Don't see any problem in achieving what you want.
Can you post a sample to exactly understand what your issue is?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:CC2448DB-9147-4391-BBDA-7891A6E3294F@.microsoft.com...
>I am losing my hair...and my mind...
> Is there any reason why I wouldn't be able to cast a varchar value of say
> 7.8 to decimal?
> I have a whole bunch of lab results that come with a bunch of garbage in
> the
> result column. I have stripped it away so that it is only a format
> [1-x].[0-9]. I want to make it a number so I can identify High and low
> values
> for each patient.
> Am I missing something...besides my mind?
> Thanks in advance,
>
>|||Here is the code... pretty straight forward....
There is something still contained in the string that is messing up the
cast. (See raw data below code)
select top 7
patientid,
decodedvalue,
convert(varchar,replace(replace(replace(
decodedvalue,'
',''),'>',''),'%','')) value,
-- cast(convert(varchar,replace(replace(r
eplace(decodedvalue,'
',''),'>',''),'%','')) as float) value,
len(replace(replace(replace(decodedvalue
,' ',''),'>',''),'%','')) str_length
from
#diabetes_results
where
decodedvalue like '%.%'
order by
patientid
58 6.8 % 6.8 3
58 7.6 % 7.6 3
58 6.7 % 6.7 3
58 7.1 % 7.1 3
58 6.2 % 6.2 3
168 7.5 % 7.5 3
168 7.5 7.5 5
Note the length of '5' in the final record though it is obvious the length
should be 3. I trimmed the column of spaces but they remain.
Any thoughts?
Thanks in advance,
"SriSamp" wrote:
> Don't see any problem in achieving what you want.
> Can you post a sample to exactly understand what your issue is?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:CC2448DB-9147-4391-BBDA-7891A6E3294F@.microsoft.com...
>
>|||168 7.5 7.5 5
Perhaps those aren't spaces - in fact I think they are one carriage return
and one line feed character. How are these values inserted? You should
prevent illegal values from being entered at all.
In the mean time - remove char(13) and char(10) from the string.
ML
http://milambda.blogspot.com/
cast question
if i declare a variable within a stored procedure as a varchar, then later
cast it to an integer- will it act as an integer for all subsequent
references after the cast?
example:
declare @.myVar varchar(2)
set @.myVar = '2'
set @.myVar = cast(@.myVar as integer)
select @.myVar -- what data type is my variable now? is it an integer
because of the previous cast?
or would i need to specify: "select
cast(@.myVar as integer)" in order for it to be considered an integer?
thanks much,
jTJT,
DECLARE @.MYVAR VARCHAR(2)
SET @.MYVAR = '2'
SELECT ISNUMERIC(@.MYVAR)
SELECT 'THIS IS A TEST ' + @.MYVAR
SET @.MYVAR = CAST(@.MYVAR AS INTEGER)
SELECT ISNUMERIC(@.MYVAR)
SELECT 'THIS IS A TEST ' + @.MYVAR
RESULTS:
1
THIS IS A TEST 2
1
THIS IS A TEST 2
HTH
Jerry
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>|||Since you declared it to be varchar(2), that's how it will remain for the
batch.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
if i declare a variable within a stored procedure as a varchar, then later
cast it to an integer- will it act as an integer for all subsequent
references after the cast?
example:
declare @.myVar varchar(2)
set @.myVar = '2'
set @.myVar = cast(@.myVar as integer)
select @.myVar -- what data type is my variable now? is it an integer
because of the previous cast?
or would i need to specify: "select
cast(@.myVar as integer)" in order for it to be considered an integer?
thanks much,
jT|||Note however that this will cause the string concatenation to fail where the
others worked:
SELECT 'THIS IS A TEST ' + CAST(@.MYVAR AS INTEGER)
Results:
Server: Msg 245, Level 16, State 1, Line 7
Syntax error converting the varchar value 'THIS IS A TEST ' to a column of
data type int.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsPhT1zFHA.3896@.TK2MSFTNGP10.phx.gbl...
> JT,
> DECLARE @.MYVAR VARCHAR(2)
> SET @.MYVAR = '2'
> SELECT ISNUMERIC(@.MYVAR)
> SELECT 'THIS IS A TEST ' + @.MYVAR
> SET @.MYVAR = CAST(@.MYVAR AS INTEGER)
> SELECT ISNUMERIC(@.MYVAR)
> SELECT 'THIS IS A TEST ' + @.MYVAR
> RESULTS:
> --
> 1
>
> --
> THIS IS A TEST 2
> --
> 1
> --
> THIS IS A TEST 2
> HTH
> Jerry
> "JT" <jt@.nospam.com> wrote in message
> news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||thanks much!
jT
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e1hiGX1zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Note however that this will cause the string concatenation to fail where
the
> others worked:
> SELECT 'THIS IS A TEST ' + CAST(@.MYVAR AS INTEGER)
> Results:
> Server: Msg 245, Level 16, State 1, Line 7
> Syntax error converting the varchar value 'THIS IS A TEST ' to a column of
> data type int.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsPhT1zFHA.3896@.TK2MSFTNGP10.phx.gbl...
integer
>|||Just to add, you are casting the value in @.myVar to integer, not the @.myVar
variable. Note also that you don't have to cast it to an integer just get
use it as an integer. If you said
select 1 + '1'
It will return:
2
But if you enter
select 1 + 'bob'
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'bob' to a column of data type
int.
It shouts "WRONG" at you, just as if you did:
select cast('bob' as int)
So be careful with this sort of operaton, because when you cast a varchar to
an int it will not just return NULL, it gives an error.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>|||In addition to the other posts, check out "data type precedence" in Books On
line. Will probably
explain a lot for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JT" <jt@.nospam.com> wrote in message news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...arkred">
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>
cast it to an integer- will it act as an integer for all subsequent
references after the cast?
example:
declare @.myVar varchar(2)
set @.myVar = '2'
set @.myVar = cast(@.myVar as integer)
select @.myVar -- what data type is my variable now? is it an integer
because of the previous cast?
or would i need to specify: "select
cast(@.myVar as integer)" in order for it to be considered an integer?
thanks much,
jTJT,
DECLARE @.MYVAR VARCHAR(2)
SET @.MYVAR = '2'
SELECT ISNUMERIC(@.MYVAR)
SELECT 'THIS IS A TEST ' + @.MYVAR
SET @.MYVAR = CAST(@.MYVAR AS INTEGER)
SELECT ISNUMERIC(@.MYVAR)
SELECT 'THIS IS A TEST ' + @.MYVAR
RESULTS:
1
THIS IS A TEST 2
1
THIS IS A TEST 2
HTH
Jerry
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>|||Since you declared it to be varchar(2), that's how it will remain for the
batch.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
if i declare a variable within a stored procedure as a varchar, then later
cast it to an integer- will it act as an integer for all subsequent
references after the cast?
example:
declare @.myVar varchar(2)
set @.myVar = '2'
set @.myVar = cast(@.myVar as integer)
select @.myVar -- what data type is my variable now? is it an integer
because of the previous cast?
or would i need to specify: "select
cast(@.myVar as integer)" in order for it to be considered an integer?
thanks much,
jT|||Note however that this will cause the string concatenation to fail where the
others worked:
SELECT 'THIS IS A TEST ' + CAST(@.MYVAR AS INTEGER)
Results:
Server: Msg 245, Level 16, State 1, Line 7
Syntax error converting the varchar value 'THIS IS A TEST ' to a column of
data type int.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsPhT1zFHA.3896@.TK2MSFTNGP10.phx.gbl...
> JT,
> DECLARE @.MYVAR VARCHAR(2)
> SET @.MYVAR = '2'
> SELECT ISNUMERIC(@.MYVAR)
> SELECT 'THIS IS A TEST ' + @.MYVAR
> SET @.MYVAR = CAST(@.MYVAR AS INTEGER)
> SELECT ISNUMERIC(@.MYVAR)
> SELECT 'THIS IS A TEST ' + @.MYVAR
> RESULTS:
> --
> 1
>
> --
> THIS IS A TEST 2
> --
> 1
> --
> THIS IS A TEST 2
> HTH
> Jerry
> "JT" <jt@.nospam.com> wrote in message
> news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||thanks much!
jT
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e1hiGX1zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Note however that this will cause the string concatenation to fail where
the
> others worked:
> SELECT 'THIS IS A TEST ' + CAST(@.MYVAR AS INTEGER)
> Results:
> Server: Msg 245, Level 16, State 1, Line 7
> Syntax error converting the varchar value 'THIS IS A TEST ' to a column of
> data type int.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsPhT1zFHA.3896@.TK2MSFTNGP10.phx.gbl...
integer
>|||Just to add, you are casting the value in @.myVar to integer, not the @.myVar
variable. Note also that you don't have to cast it to an integer just get
use it as an integer. If you said
select 1 + '1'
It will return:
2
But if you enter
select 1 + 'bob'
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'bob' to a column of data type
int.
It shouts "WRONG" at you, just as if you did:
select cast('bob' as int)
So be careful with this sort of operaton, because when you cast a varchar to
an int it will not just return NULL, it gives an error.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>|||In addition to the other posts, check out "data type precedence" in Books On
line. Will probably
explain a lot for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JT" <jt@.nospam.com> wrote in message news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...arkred">
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>
cast or convert varchar to money/datetime
Hello,
I have a dataset that is all varchar with leading and
trailing spaces (comes from a mainframe). I import this
data to a table that is all varchar (using DTS). I then
insert it to a table that has the correct datatype fields,
but I have to perform a conversion. I have been using
Cast(ltrim(rtrim(colx)) As datetime)
Cast(ltrim(rtrim(colx)) As money)
I seem to be getting the correct data with datetime, and
with money I get like 306.6900. Am I supposed to get a $
symbol for money? Or just decimal? So is cast the
correct operator here or should I use convert? If
convert - how do I convert money?
Thanks,
RonThere is no reason to store the dollar sign with the numeric value, nor is
there a need to use the MONEY data type. In fact, this can cause problems.
See http://www.aspfaq.com/2503 which, among other things, describes reasons
to use DECIMAL in favor of MONEY/SMALLMONEY.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3601c53611$48d3c830$a401280a@.phx.gbl...
> Hello,
> I have a dataset that is all varchar with leading and
> trailing spaces (comes from a mainframe). I import this
> data to a table that is all varchar (using DTS). I then
> insert it to a table that has the correct datatype fields,
> but I have to perform a conversion. I have been using
> Cast(ltrim(rtrim(colx)) As datetime)
> Cast(ltrim(rtrim(colx)) As money)
> I seem to be getting the correct data with datetime, and
> with money I get like 306.6900. Am I supposed to get a $
> symbol for money? Or just decimal? So is cast the
> correct operator here or should I use convert? If
> convert - how do I convert money?
> Thanks,
> Ron|||SQL Server stores data, not the presentation of data. The client application
is what is doing the
presentation of your data. for the money datatype, the values 306.6900 and 3
06.69 are the same.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3601c53611$48d3c830$a401280a@.phx.gbl...
> Hello,
> I have a dataset that is all varchar with leading and
> trailing spaces (comes from a mainframe). I import this
> data to a table that is all varchar (using DTS). I then
> insert it to a table that has the correct datatype fields,
> but I have to perform a conversion. I have been using
> Cast(ltrim(rtrim(colx)) As datetime)
> Cast(ltrim(rtrim(colx)) As money)
> I seem to be getting the correct data with datetime, and
> with money I get like 306.6900. Am I supposed to get a $
> symbol for money? Or just decimal? So is cast the
> correct operator here or should I use convert? If
> convert - how do I convert money?
> Thanks,
> Ron|||Ron,
[money] values are numbers. 306.6900 is a number. The $ symbol
is not part of a money value, but just something that may be part of the
display of a number. SQL Server can convert money values to
strings with $ or with commas, using CONVERT with format codes
(see CAST AND CONVERT in Books Online).
The type of Cast(whatever as money) will definitely be [money]. If
you want to be absolutely sure, try
select cast(whatever as money) as onlyColumn
into #checktype
then look at the table structure for #checktype.
Steve Kass
Drew University
Ron wrote:
>Hello,
>I have a dataset that is all varchar with leading and
>trailing spaces (comes from a mainframe). I import this
>data to a table that is all varchar (using DTS). I then
>insert it to a table that has the correct datatype fields,
>but I have to perform a conversion. I have been using
>Cast(ltrim(rtrim(colx)) As datetime)
>Cast(ltrim(rtrim(colx)) As money)
>I seem to be getting the correct data with datetime, and
>with money I get like 306.6900. Am I supposed to get a $
>symbol for money? Or just decimal? So is cast the
>correct operator here or should I use convert? If
>convert - how do I convert money?
>Thanks,
>Ron
>
I have a dataset that is all varchar with leading and
trailing spaces (comes from a mainframe). I import this
data to a table that is all varchar (using DTS). I then
insert it to a table that has the correct datatype fields,
but I have to perform a conversion. I have been using
Cast(ltrim(rtrim(colx)) As datetime)
Cast(ltrim(rtrim(colx)) As money)
I seem to be getting the correct data with datetime, and
with money I get like 306.6900. Am I supposed to get a $
symbol for money? Or just decimal? So is cast the
correct operator here or should I use convert? If
convert - how do I convert money?
Thanks,
RonThere is no reason to store the dollar sign with the numeric value, nor is
there a need to use the MONEY data type. In fact, this can cause problems.
See http://www.aspfaq.com/2503 which, among other things, describes reasons
to use DECIMAL in favor of MONEY/SMALLMONEY.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3601c53611$48d3c830$a401280a@.phx.gbl...
> Hello,
> I have a dataset that is all varchar with leading and
> trailing spaces (comes from a mainframe). I import this
> data to a table that is all varchar (using DTS). I then
> insert it to a table that has the correct datatype fields,
> but I have to perform a conversion. I have been using
> Cast(ltrim(rtrim(colx)) As datetime)
> Cast(ltrim(rtrim(colx)) As money)
> I seem to be getting the correct data with datetime, and
> with money I get like 306.6900. Am I supposed to get a $
> symbol for money? Or just decimal? So is cast the
> correct operator here or should I use convert? If
> convert - how do I convert money?
> Thanks,
> Ron|||SQL Server stores data, not the presentation of data. The client application
is what is doing the
presentation of your data. for the money datatype, the values 306.6900 and 3
06.69 are the same.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3601c53611$48d3c830$a401280a@.phx.gbl...
> Hello,
> I have a dataset that is all varchar with leading and
> trailing spaces (comes from a mainframe). I import this
> data to a table that is all varchar (using DTS). I then
> insert it to a table that has the correct datatype fields,
> but I have to perform a conversion. I have been using
> Cast(ltrim(rtrim(colx)) As datetime)
> Cast(ltrim(rtrim(colx)) As money)
> I seem to be getting the correct data with datetime, and
> with money I get like 306.6900. Am I supposed to get a $
> symbol for money? Or just decimal? So is cast the
> correct operator here or should I use convert? If
> convert - how do I convert money?
> Thanks,
> Ron|||Ron,
[money] values are numbers. 306.6900 is a number. The $ symbol
is not part of a money value, but just something that may be part of the
display of a number. SQL Server can convert money values to
strings with $ or with commas, using CONVERT with format codes
(see CAST AND CONVERT in Books Online).
The type of Cast(whatever as money) will definitely be [money]. If
you want to be absolutely sure, try
select cast(whatever as money) as onlyColumn
into #checktype
then look at the table structure for #checktype.
Steve Kass
Drew University
Ron wrote:
>Hello,
>I have a dataset that is all varchar with leading and
>trailing spaces (comes from a mainframe). I import this
>data to a table that is all varchar (using DTS). I then
>insert it to a table that has the correct datatype fields,
>but I have to perform a conversion. I have been using
>Cast(ltrim(rtrim(colx)) As datetime)
>Cast(ltrim(rtrim(colx)) As money)
>I seem to be getting the correct data with datetime, and
>with money I get like 306.6900. Am I supposed to get a $
>symbol for money? Or just decimal? So is cast the
>correct operator here or should I use convert? If
>convert - how do I convert money?
>Thanks,
>Ron
>
Friday, February 10, 2012
cast from float to varchar
Hi,
Can I convert from float to varchar without trunc the values? Can I use any mask like '#.##'?
from -> cast ( 123.44 as varchar(256) )
result = '123.44'
thanks,
Hi Alessandro,
As long as the varchar type you are casting to is long enough, no truncation of the float value will occur.
For example, cast(23.444 as varchar(3)) will result in a overflow, where cast(23.444 as varchar(6)) will return correctly.
Is that what you meant?
Cheers,
Rob
Subscribe to:
Posts (Atom)