Renaming column

ALTER TABLE ISMPBI CHANGE `PBICCGRFNUM` `PBICCGRFNUM1` BIGINT NOT NULL;
this query to rename column name in my sql but not working in sql server
so what is exact syntax to rename columnSee the syntax for the ALTER TABLE command in the online manual (http://msdn2.microsoft.com/en-us/library/ms190273.aspx)

Btw: don't use the backticks. They are a MySQL "speciality" and do not work with any other DBMS (actually they are not needed in MySQL as well)|||MS SQL SERVER does not support RENAME syntax i cant find the syntax to rename column can any one give me exact syntax to rename column|||The ALTER TABLE documentation links to the sp_rename (http://msdn2.microsoft.com/en-us/library/ms188351.aspx) function:
Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type
Example B: "renaming a column"|||thanks

here is exact syntax

EXEC sp_rename 'BFMENUDEFINITION.[GROUP]', 'MENUGROUP', 'COLUMN';

'BFMENUDEFINITION== tablename

GROUP == old column

'MENUGROUP'== new columnsql

Renaming a column and replacing references to the old column name

Is there a utility out there to allow be able to rename a column in a table
and then scan the database for any objects (procs, functions, views) that us
e
this column and rename them as well. we have a db with some badly named
columns that are confusing the hell out of developers and want to rename
globally.That would be not easy to do this automatically, if two columns have the sam
e
name (refering to your poor designed database) you can do something like a
search % replace). The safest way would be to iterate (manually) rhough all
the object and change that.
Sorry about that.
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a tabl
e
> and then scan the database for any objects (procs, functions, views) that
use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.|||You can use Enterprise Manager to generate creation scripts for all
objects, then search and replace the names and re-run the scripts.
I recommend you keep all your scripts in a source control system - then
you minimize the need to go back to the database to find the objects to
be changed.
David Portas
SQL Server MVP
--|||This is how I do this:
For a start I have all my source code within source control.
I change the create statement in question and run a build using to see what
breaks, then I can check out all the relevant scripts and make my changes
until I have no build breaks. I can then check everything back in and create
a custom script to rename the column, adding this to source control. I use D
B
Ghost (http://www.dbghost.com) and not just because I created it but because
this is the only fool proof way of introducing changes that don't break the
system with the added bonus of all the history of changes in my source
control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a tabl
e
> and then scan the database for any objects (procs, functions, views) that
use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.sql

Renaming a column and replacing references to the old column name

Is there a utility out there to allow be able to rename a column in a table
and then scan the database for any objects (procs, functions, views) that use
this column and rename them as well. we have a db with some badly named
columns that are confusing the hell out of developers and want to rename
globally.
That would be not easy to do this automatically, if two columns have the same
name (refering to your poor designed database) you can do something like a
search % replace). The safest way would be to iterate (manually) rhough all
the object and change that.
Sorry about that.
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.
|||You can use Enterprise Manager to generate creation scripts for all
objects, then search and replace the names and re-run the scripts.
I recommend you keep all your scripts in a source control system - then
you minimize the need to go back to the database to find the objects to
be changed.
David Portas
SQL Server MVP
|||This is how I do this:
For a start I have all my source code within source control.
I change the create statement in question and run a build using to see what
breaks, then I can check out all the relevant scripts and make my changes
until I have no build breaks. I can then check everything back in and create
a custom script to rename the column, adding this to source control. I use DB
Ghost (http://www.dbghost.com) and not just because I created it but because
this is the only fool proof way of introducing changes that don't break the
system with the added bonus of all the history of changes in my source
control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"markbate" wrote:

> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.

Renaming a column and replacing references to the old column name

Is there a utility out there to allow be able to rename a column in a table
and then scan the database for any objects (procs, functions, views) that use
this column and rename them as well. we have a db with some badly named
columns that are confusing the hell out of developers and want to rename
globally.That would be not easy to do this automatically, if two columns have the same
name (refering to your poor designed database) you can do something like a
search % replace). The safest way would be to iterate (manually) rhough all
the object and change that.
Sorry about that.
"markbate" wrote:
> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.|||example - we want to rename a column called Deleted - to Active. Then scan
all procs to see where this table.column combination is used in any procs,
functions, views etc and rename as well, hope that is a little clearer. There
must be a utility out there that does this but I cany find one. thanks
"Jens Sü�meyer" wrote:
> That would be not easy to do this automatically, if two columns have the same
> name (refering to your poor designed database) you can do something like a
> search % replace). The safest way would be to iterate (manually) rhough all
> the object and change that.
> Sorry about that.
> "markbate" wrote:
> > Is there a utility out there to allow be able to rename a column in a table
> > and then scan the database for any objects (procs, functions, views) that use
> > this column and rename them as well. we have a db with some badly named
> > columns that are confusing the hell out of developers and want to rename
> > globally.|||You can use Enterprise Manager to generate creation scripts for all
objects, then search and replace the names and re-run the scripts.
I recommend you keep all your scripts in a source control system - then
you minimize the need to go back to the database to find the objects to
be changed.
--
David Portas
SQL Server MVP
--|||Search & Replace will work if you can rely on your definitions.
You want to change the name "Active" to "Deleted" on a special table. You
run S&R that´ll will change also that columns in other which should not be
changed (You should keep a hand on the automatic process), that can bring you
some weird problems if you don´t do that in a accurate way.
HTH, jens Suessmeyer.
"markbate" wrote:
> example - we want to rename a column called Deleted - to Active. Then scan
> all procs to see where this table.column combination is used in any procs,
> functions, views etc and rename as well, hope that is a little clearer. There
> must be a utility out there that does this but I cany find one. thanks
> "Jens Sü�meyer" wrote:
> > That would be not easy to do this automatically, if two columns have the same
> > name (refering to your poor designed database) you can do something like a
> > search % replace). The safest way would be to iterate (manually) rhough all
> > the object and change that.
> >
> > Sorry about that.
> >
> > "markbate" wrote:
> >
> > > Is there a utility out there to allow be able to rename a column in a table
> > > and then scan the database for any objects (procs, functions, views) that use
> > > this column and rename them as well. we have a db with some badly named
> > > columns that are confusing the hell out of developers and want to rename
> > > globally.|||This is how I do this:
For a start I have all my source code within source control.
I change the create statement in question and run a build using to see what
breaks, then I can check out all the relevant scripts and make my changes
until I have no build breaks. I can then check everything back in and create
a custom script to rename the column, adding this to source control. I use DB
Ghost (http://www.dbghost.com) and not just because I created it but because
this is the only fool proof way of introducing changes that don't break the
system with the added bonus of all the history of changes in my source
control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"markbate" wrote:
> Is there a utility out there to allow be able to rename a column in a table
> and then scan the database for any objects (procs, functions, views) that use
> this column and rename them as well. we have a db with some badly named
> columns that are confusing the hell out of developers and want to rename
> globally.

Rename Table

How do you rename a column in SQL I have tried everything.Lookup sp_rename in the BOL
Thomas
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:809E5B93-49BA-479D-B5A7-A9F9B18FDF04@.microsoft.com...
> How do you rename a column in SQL I have tried everything.|||Use System Stored Proc called sp_rename
sp_rename [ @.objname = ] 'object_name' ,
[ @.newname = ] 'new_name'
[ , [ @.objtype = ] 'object_type' ]
"Lontae Jones" wrote:

> How do you rename a column in SQL I have tried everything.|||Do you want to rename a table or a column? Your subject says table but your
messages says column.
To rename a column, it can be confusing. You have to specify both the table
and column name for the old name, but for the new name you just specify the
column name:
exec sp_rename 'table_name.original_column_name', 'new_column_name'
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:809E5B93-49BA-479D-B5A7-A9F9B18FDF04@.microsoft.com...
> How do you rename a column in SQL I have tried everything.|||Here is a short example for you.
Thanks
set nocount on
go
create table t(col int)
go
insert t values(1)
insert t values(2)
insert t values(3)
insert t values(4)
go
select * from t
go
exec sp_rename 't','u'
go
print 'renamed table u'
select * from u
go
print 'renamed column col as newcol'
EXEC sp_rename 'u.col', 'newcol', 'COLUMN'
select * from u
go
drop table u
go
http://zulfiqar.typepad.com
MCP
"Lontae Jones" wrote:

> How do you rename a column in SQL I have tried everything.

rename NULL column outputs

I have this view:
CREATE VIEW CautionDescriptions AS
SELECT TOP 100 PERCENT
Oils.OilName AS [Oil name], Cautions.Description AS Caution
FROM Cautions
INNER JOIN OilCautions ON Cautions.CautionID = OilCautions.CautionID
RIGHT OUTER JOIN Oils ON OilCautions.OilID = Oils.OilID
ORDER BY Oils.OilName
I want to return all rows even those with NULLs in the description key. Is
there a way to replace the <NULL>s in the description column with more
attractive text?
like
if OilCautions.OilID ISNULL 'No description available'
endif
Where could I put this in the SELECT or FROM clause?SELECT COALESCE(column_name, 'No description available')
You can also use ISNULL. Funny, I just wrote an article about this earlier
today:
http://www.aspfaq.com/2532
http://www.aspfaq.com/
(Reverse address to reply.)
"Patrick" <psully@.nospam.eatel.net> wrote in message
news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
> I have this view:
> CREATE VIEW CautionDescriptions AS
> SELECT TOP 100 PERCENT
> Oils.OilName AS [Oil name], Cautions.Description AS Caution
> FROM Cautions
> INNER JOIN OilCautions ON Cautions.CautionID = OilCautions.CautionID
> RIGHT OUTER JOIN Oils ON OilCautions.OilID = Oils.OilID
> ORDER BY Oils.OilName
> I want to return all rows even those with NULLs in the description key. Is
> there a way to replace the <NULL>s in the description column with more
> attractive text?
> like
> if OilCautions.OilID ISNULL 'No description available'
> endif
> Where could I put this in the SELECT or FROM clause?
>
>|||Thanks Aaron! That was a fast answer. I never heard of coalesce. I am going
to read your article too, thanks again!
BTW, the tables and excercises are from MS's Step by Step SQL Server 2000
Programming. The book name is certainly a misnomer, the book is mostly about
using Enterprise Manager.
Patrick
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#rYiovYAFHA.3744@.TK2MSFTNGP15.phx.gbl...
> SELECT COALESCE(column_name, 'No description available')
> You can also use ISNULL. Funny, I just wrote an article about this
earlier
> today:
> http://www.aspfaq.com/2532
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Patrick" <psully@.nospam.eatel.net> wrote in message
> news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
Is
>|||Read your article. **** rating!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#rYiovYAFHA.3744@.TK2MSFTNGP15.phx.gbl...
> SELECT COALESCE(column_name, 'No description available')
> You can also use ISNULL. Funny, I just wrote an article about this
earlier
> today:
> http://www.aspfaq.com/2532
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Patrick" <psully@.nospam.eatel.net> wrote in message
> news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
Is
>|||> BTW, the tables and excercises are from MS's Step by Step SQL Server 2000
> Programming. The book name is certainly a misnomer, the book is mostly
about
> using Enterprise Manager.
Then you might want to read this article too:
http://www.aspfaq.com/2455
:-)
PS I suggest creating your views from scratch, as opposed to letting
Enterprise Manager write the code for you ... because of the limitations in
the view designer, as well as its funky quirks like putting TOP 100 PERCENT
and ORDER BY in views for some reason.|||Oops, I think I hit reply too soon (may be an empty reply here soon).
Anyway, that was an interesting and informative article. I've taken a few
SQL Server courses, and they all start of with EM. But Step by Step does
cover the QA, too. I remember vaguely when I learned how to use it a little
last year, I was impressed a lot by QA. I mainy use EM for experimenting
with joins really fast, and if it works, copy the script and edit it in QA.
When are you going to turn all these "tips" into a book? lol, thanks!
Patrick
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ur6ou3YAFHA.4072@.TK2MSFTNGP10.phx.gbl...
2000
> about
> Then you might want to read this article too:
> http://www.aspfaq.com/2455
> :-)
> PS I suggest creating your views from scratch, as opposed to letting
> Enterprise Manager write the code for you ... because of the limitations
in
> the view designer, as well as its funky quirks like putting TOP 100
PERCENT
> and ORDER BY in views for some reason.
>

Rename Columns via Query

I have a table with 9 columns in it - ID, ColA, ColB, etc. I'm trying to use
a view to display the ID column,and only one other, which will vary depending
on my requirements. I have another table that contains only one row (and is
updated regularly) with the column name (ColID) I'm trying to retieve.
Can anyone tell me how I can pass the value of ColID into the column name in
my view?
I hope this is clear. Thanks in anticipation...
Take a look at this example:
USE Northwind
CREATE TABLE QueryColumn
(qc sysname)
GO
INSERT INTO QueryColumn VALUES(N'CompanyName')
DECLARE @.q varchar(8000)
SET @.q='SELECT ShipperId, '+(SELECT qc FROM QueryColumn)+' FROM Shippers'
EXEC (@.q)
UPDATE QueryColumn SET qc=N'Phone'
SET @.q='SELECT ShipperId, '+(SELECT qc FROM QueryColumn)+' FROM Shippers'
EXEC (@.q)
GO
DROP TABLE QueryColumn
GO
Do please also read the article at
http://www.sommarskog.se/dynamic_sql.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"MillionChads" <MillionChads@.discussions.microsoft.com> wrote in message
news:ABCE5205-361F-4BB8-8DAC-DCBEF6DACB4C@.microsoft.com...
> I have a table with 9 columns in it - ID, ColA, ColB, etc. I'm trying to
use
> a view to display the ID column,and only one other, which will vary
depending
> on my requirements. I have another table that contains only one row (and
is
> updated regularly) with the column name (ColID) I'm trying to retieve.
> Can anyone tell me how I can pass the value of ColID into the column name
in
> my view?
> I hope this is clear. Thanks in anticipation...

rename columns in a temp table

hello
I have a stored procedure that calculates the values of column data based
on an input variable. I would like to rename the columns to the input
variables so that i can directly graph the results of the table. Is this
possible?
for example, a column in the table #summary is called year1 but i need it to
be renamed to '@.inputYear - 5'. So if @.inputYear = 2000, i need the column
to be renamed to 1995.
thanks for any and all help!
benBen wrote:
> hello
> I have a stored procedure that calculates the values of column data
> based on an input variable. I would like to rename the columns to
> the input variables so that i can directly graph the results of the
> table. Is this possible?
> for example, a column in the table #summary is called year1 but i
> need it to be renamed to '@.inputYear - 5'. So if @.inputYear = 2000,
> i need the column to be renamed to 1995.
> thanks for any and all help!
> ben
You can use an alias for the column, but will have to use dynamic sql.
Or you can return two result sets. The first is the name of the columns,
the second is the data to graph.
EXEC ('Select year1 as "' + CAST(@.inputYear - 5 as VARCHAR(4)) + '"
From...')
David Gugick
Quest Software
www.imceda.com
www.quest.com

rename column with check constraints

I want to change the name of a column. I tried to use sp_rename but i get the message:
"Object 'table2.TelNoMob' cannot be renamed because the object participates in enforced dependencies."

I think this is because the specific column has check constraint.

Is there a way of changing the column's name?You will have to drop the constraint and add it it again afterwards.

This will give you the constraint name to drop

select object_name(constid) from sysconstraints where id = object_id('tblname')
and colid = (select colid from syscolumns where id = object_id('tblname') and name = 'colname')

Rename Column in Very large table

I would like to rename a column in a table that has over 770 million rows an
d
around 300-400 gigs of data. I would like to minimize time, resources, and
risk. I am looking for suggestions, risks, or any other gotcha's i may not b
e
considering.Check out sp_rename in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>I would like to rename a column in a table that has over 770 million rows
>and
> around 300-400 gigs of data. I would like to minimize time, resources, and
> risk. I am looking for suggestions, risks, or any other gotcha's i may not
> be
> considering.|||Does this have any impact in the size of the transaction log, or since i
believe it uses tempdb will it cause any errors due to log file size or disc
space issues?
in Enterprise manager in design mode the gui will handle the name change but
i'm more worried about the impact it may have to log file size ...ect
"Tom Moreau" wrote:

> Check out sp_rename in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>
>|||An object rename goes very quickly and has practically no impact on the log.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...[vbcol=seagreen]
> Does this have any impact in the size of the transaction log, or since i
> believe it uses tempdb will it cause any errors due to log file size or
> disc
> space issues?
> in Enterprise manager in design mode the gui will handle the name change
> but
> i'm more worried about the impact it may have to log file size ...ect
> "Tom Moreau" wrote:
>|||How about the TempDB size, what effect does this have on the size of this db
.
Do you need atleast the same size of free space on the tempdb to successfull
y
complete the rename?
"Tom Moreau" wrote:

> An object rename goes very quickly and has practically no impact on the lo
g.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...
>
>|||There is no impact on tempdb during an object rename.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:ED637B0F-7A3E-40B5-81E2-B6445C9D5381@.microsoft.com...[vbcol=seagreen]
> How about the TempDB size, what effect does this have on the size of this
> db.
> Do you need atleast the same size of free space on the tempdb to
> successfully
> complete the rename?
> "Tom Moreau" wrote:
>

Rename Column in Very large table

I would like to rename a column in a table that has over 770 million rows and
around 300-400 gigs of data. I would like to minimize time, resources, and
risk. I am looking for suggestions, risks, or any other gotcha's i may not be
considering.
Check out sp_rename in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>I would like to rename a column in a table that has over 770 million rows
>and
> around 300-400 gigs of data. I would like to minimize time, resources, and
> risk. I am looking for suggestions, risks, or any other gotcha's i may not
> be
> considering.
|||Does this have any impact in the size of the transaction log, or since i
believe it uses tempdb will it cause any errors due to log file size or disc
space issues?
in Enterprise manager in design mode the gui will handle the name change but
i'm more worried about the impact it may have to log file size ...ect
"Tom Moreau" wrote:

> Check out sp_rename in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>
>
|||An object rename goes very quickly and has practically no impact on the log.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...[vbcol=seagreen]
> Does this have any impact in the size of the transaction log, or since i
> believe it uses tempdb will it cause any errors due to log file size or
> disc
> space issues?
> in Enterprise manager in design mode the gui will handle the name change
> but
> i'm more worried about the impact it may have to log file size ...ect
> "Tom Moreau" wrote:
|||How about the TempDB size, what effect does this have on the size of this db.
Do you need atleast the same size of free space on the tempdb to successfully
complete the rename?
"Tom Moreau" wrote:

> An object rename goes very quickly and has practically no impact on the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...
>
>
|||There is no impact on tempdb during an object rename.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:ED637B0F-7A3E-40B5-81E2-B6445C9D5381@.microsoft.com...[vbcol=seagreen]
> How about the TempDB size, what effect does this have on the size of this
> db.
> Do you need atleast the same size of free space on the tempdb to
> successfully
> complete the rename?
> "Tom Moreau" wrote:

Rename Column in Very large table

I would like to rename a column in a table that has over 770 million rows and
around 300-400 gigs of data. I would like to minimize time, resources, and
risk. I am looking for suggestions, risks, or any other gotcha's i may not be
considering.Check out sp_rename in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>I would like to rename a column in a table that has over 770 million rows
>and
> around 300-400 gigs of data. I would like to minimize time, resources, and
> risk. I am looking for suggestions, risks, or any other gotcha's i may not
> be
> considering.|||Does this have any impact in the size of the transaction log, or since i
believe it uses tempdb will it cause any errors due to log file size or disc
space issues?
in Enterprise manager in design mode the gui will handle the name change but
i'm more worried about the impact it may have to log file size ...ect
"Tom Moreau" wrote:
> Check out sp_rename in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
> >I would like to rename a column in a table that has over 770 million rows
> >and
> > around 300-400 gigs of data. I would like to minimize time, resources, and
> > risk. I am looking for suggestions, risks, or any other gotcha's i may not
> > be
> > considering.
>
>|||An object rename goes very quickly and has practically no impact on the log.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...
> Does this have any impact in the size of the transaction log, or since i
> believe it uses tempdb will it cause any errors due to log file size or
> disc
> space issues?
> in Enterprise manager in design mode the gui will handle the name change
> but
> i'm more worried about the impact it may have to log file size ...ect
> "Tom Moreau" wrote:
>> Check out sp_rename in the BOL.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
>> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>> >I would like to rename a column in a table that has over 770 million
>> >rows
>> >and
>> > around 300-400 gigs of data. I would like to minimize time, resources,
>> > and
>> > risk. I am looking for suggestions, risks, or any other gotcha's i may
>> > not
>> > be
>> > considering.
>>|||How about the TempDB size, what effect does this have on the size of this db.
Do you need atleast the same size of free space on the tempdb to successfully
complete the rename?
"Tom Moreau" wrote:
> An object rename goes very quickly and has practically no impact on the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...
> > Does this have any impact in the size of the transaction log, or since i
> > believe it uses tempdb will it cause any errors due to log file size or
> > disc
> > space issues?
> >
> > in Enterprise manager in design mode the gui will handle the name change
> > but
> > i'm more worried about the impact it may have to log file size ...ect
> >
> > "Tom Moreau" wrote:
> >
> >> Check out sp_rename in the BOL.
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Columnist, SQL Server Professional
> >> Toronto, ON Canada
> >> www.pinpub.com
> >>
> >> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
> >> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
> >> >I would like to rename a column in a table that has over 770 million
> >> >rows
> >> >and
> >> > around 300-400 gigs of data. I would like to minimize time, resources,
> >> > and
> >> > risk. I am looking for suggestions, risks, or any other gotcha's i may
> >> > not
> >> > be
> >> > considering.
> >>
> >>
> >>
>
>|||There is no impact on tempdb during an object rename.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
news:ED637B0F-7A3E-40B5-81E2-B6445C9D5381@.microsoft.com...
> How about the TempDB size, what effect does this have on the size of this
> db.
> Do you need atleast the same size of free space on the tempdb to
> successfully
> complete the rename?
> "Tom Moreau" wrote:
>> An object rename goes very quickly and has practically no impact on the
>> log.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
>> news:D8CC0545-AFF3-4359-BDF9-01993145422D@.microsoft.com...
>> > Does this have any impact in the size of the transaction log, or since
>> > i
>> > believe it uses tempdb will it cause any errors due to log file size or
>> > disc
>> > space issues?
>> >
>> > in Enterprise manager in design mode the gui will handle the name
>> > change
>> > but
>> > i'm more worried about the impact it may have to log file size ...ect
>> >
>> > "Tom Moreau" wrote:
>> >
>> >> Check out sp_rename in the BOL.
>> >>
>> >> --
>> >> Tom
>> >>
>> >> ----
>> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> SQL Server MVP
>> >> Columnist, SQL Server Professional
>> >> Toronto, ON Canada
>> >> www.pinpub.com
>> >>
>> >> "Goitzy" <Goitzy@.discussions.microsoft.com> wrote in message
>> >> news:8BBC6D10-B44C-4E3E-A3A3-5BAD7DB3C8B2@.microsoft.com...
>> >> >I would like to rename a column in a table that has over 770 million
>> >> >rows
>> >> >and
>> >> > around 300-400 gigs of data. I would like to minimize time,
>> >> > resources,
>> >> > and
>> >> > risk. I am looking for suggestions, risks, or any other gotcha's i
>> >> > may
>> >> > not
>> >> > be
>> >> > considering.
>> >>
>> >>
>> >>
>>

rename column

i need t-sql syntax for renaming a column.
the column name contains the [] characters.
alter table <tablename> rename [old field name] to NewFieldName
isn't working for me.
tia,
mcnewsxpthis works:
EXEC sp_rename 'MyTable.[Old Field]', 'NewField', 'COLUMN'|||use sp_rename..
I haven't seen the sytax that you have used.|||You might want to check this out ::
http://vadivel.blogspot.com/2004/08...g-sprename.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"mcnews" wrote:

> i need t-sql syntax for renaming a column.
> the column name contains the [] characters.
> alter table <tablename> rename [old field name] to NewFieldName
> isn't working for me.
> tia,
> mcnewsxp
>sql

Rename a primary key column

hi i am trying to rename a primary using sp_rename. but it keeps failing with
message that the column is involved in dependencies. but i have already
dropped all constraints on this table! any help please
On Tue, 31 May 2005 18:04:02 -0700, "sql guy" <sql
guy@.discussions.microsoft.com> wrote:

>hi i am trying to rename a primary using sp_rename. but it keeps failing with
>message that the column is involved in dependencies. but i have already
>dropped all constraints on this table! any help please
Hi sql guy,
Are there any foreign key constraints on other table that reference the
primary key column you're trying to rename?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi hugo, yes there are loads of them. however, i finally resolved it all
yesterday. thanks for the reply.
cheers
"Hugo Kornelis" wrote:

> On Tue, 31 May 2005 18:04:02 -0700, "sql guy" <sql
> guy@.discussions.microsoft.com> wrote:
>
> Hi sql guy,
> Are there any foreign key constraints on other table that reference the
> primary key column you're trying to rename?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Re-name a Column in an Anonymous Merge Publication

Upsized an Access .MDB and Published successfully, EXCEPT forgot to make
visible the sysobjects, so two Tables each have four Columns named: Expr1,
Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
I get errors like this:
'tblNoteAdmit' table
- Unable to rename column from 'Expr1' to 'Pump'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
table because it is published for replication.
It looks like time to Create a Script, that the Snapshot Agent can
understand ...
Sometime back, I blundered through that process to Add a Table
(Successfully) but am having trouble finding the SP_ to trick Column Names.
Aubrey Kelley
How do I Rename a Column in an Active Replicated Database?
24 hr update: Got this far: (SP is sp_rename, but still blocked by
Replication.)
USE CareData
GO
EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
Cannot rename the table because it is published for replication.
Aubrey Kelley
"Aubrey" wrote:

> Upsized an Access .MDB and Published successfully, EXCEPT forgot to make
> visible the sysobjects, so two Tables each have four Columns named: Expr1,
> Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
> I get errors like this:
> 'tblNoteAdmit' table
> - Unable to rename column from 'Expr1' to 'Pump'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
> table because it is published for replication.
> It looks like time to Create a Script, that the Snapshot Agent can
> understand ...
> Sometime back, I blundered through that process to Add a Table
> (Successfully) but am having trouble finding the SP_ to trick Column Names.
> --
> Aubrey Kelley
|||You will have to do sp_repladdcolumn to add a dummy column with the same
data type as the column you wish to rename. Then update this temp column
with the values in the column you are wishing to rename. Then drop the
column you wish to rename using sp_repldropcolumn. Then readd the column
with the new name using sp_repladdcolumn. Then update this column with the
values in the temp column. Then drop the temp column using
sp_repldropcolumn.
Alternatively you may wish to create a temp table with the values in the
column you wish to rename along with the PK. Then drop the column you wish
to rename using sp_repldropcolumn, and then add it back with the new name
using sp_repladdcolumn. Update this new column with the values in the temp
table.
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
"Aubrey" <miscuates@.online.nospam> wrote in message
news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...[vbcol=seagreen]
> How do I Rename a Column in an Active Replicated Database?
> 24 hr update: Got this far: (SP is sp_rename, but still blocked by
> Replication.)
> USE CareData
> GO
> EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
> Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
> Cannot rename the table because it is published for replication.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
Expr1,[vbcol=seagreen]
Database,[vbcol=seagreen]
the[vbcol=seagreen]
Names.[vbcol=seagreen]
|||Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
That was what it looked like in your SS2K T&SR Book. Was not sure it applied
to Anonymous Merge. Will try it Real-Soon-Now ...
Aubrey Kelley
"Hilary Cotter" wrote:

> You will have to do sp_repladdcolumn to add a dummy column with the same
> data type as the column you wish to rename. Then update this temp column
> with the values in the column you are wishing to rename. Then drop the
> column you wish to rename using sp_repldropcolumn. Then readd the column
> with the new name using sp_repladdcolumn. Then update this column with the
> values in the temp column. Then drop the temp column using
> sp_repldropcolumn.
> Alternatively you may wish to create a temp table with the values in the
> column you wish to rename along with the PK. Then drop the column you wish
> to rename using sp_repldropcolumn, and then add it back with the new name
> using sp_repladdcolumn. Update this new column with the values in the temp
> table.
> --
> 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
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...
> Expr1,
> Database,
> the
> Names.
>
>
|||What I did: Opened Query Analyzer
USE CareData
GO
EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
[RUN]
Results:
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'CareData' because schema replication is performed.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
The stored procedure will still be created.
Updated Values and Tested from Remote Client. Voila! Worked like a charm.
Why did you suggest a Temp column? Seemed superfluous to Copy from OldColumn
to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
works great.
Aubrey Kelley
"Aubrey" wrote:
[vbcol=seagreen]
> Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
> That was what it looked like in your SS2K T&SR Book. Was not sure it applied
> to Anonymous Merge. Will try it Real-Soon-Now ...
> --
> Aubrey Kelley
>
> "Hilary Cotter" wrote:
|||I'm glad it worked.
The temp table solution works great if you are able to kick your users off
while you do it. If not and the table was huge, the temp table could take
come time to populate and will get progressively out of sync if people are
banging away at it. So you would loose consistency as you do it.
However, my approach will "break" the table as you are making the changes so
the app which is using this column will fail as you do your renaming.
So neither solutions are perfect - mine takes longer but might guarantee
better consistency than yours. Your method is shorter, but your method is
preferred if you can kick all of your users off your system.
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
"Aubrey" <miscuates@.online.nospam> wrote in message
news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> What I did: Opened Query Analyzer
> USE CareData
> GO
> EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
> [RUN]
> Results:
> Warning: only Subscribers running SQL Server 2000 can synchronize with
> publication 'CareData' because schema replication is performed.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object
'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> The stored procedure will still be created.
> Updated Values and Tested from Remote Client. Voila! Worked like a charm.
> Why did you suggest a Temp column? Seemed superfluous to Copy from
OldColumn[vbcol=seagreen]
> to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
> works great.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
AM?[vbcol=seagreen]
applied[vbcol=seagreen]
same[vbcol=seagreen]
column[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
wish[vbcol=seagreen]
name[vbcol=seagreen]
temp[vbcol=seagreen]
to make[vbcol=seagreen]
named:[vbcol=seagreen]
SQL[vbcol=seagreen]
rename[vbcol=seagreen]
Column[vbcol=seagreen]
|||Correct; I was fortunate that ALL Users are Merge Clients, No ONE is directly
updating the Publisher Database.
Just to Test, I added a couple of Rows while all this was running, and they
were missing the Values in the New Columns. Fortunately I had WHERE Clauses
in the Data Copy Form, Event Procedures just in case the Project Locked Up or
Aborted.
Now, onward and upward ... Have a Great Week!
Aubrey Kelley
"Hilary Cotter" wrote:

> I'm glad it worked.
> The temp table solution works great if you are able to kick your users off
> while you do it. If not and the table was huge, the temp table could take
> come time to populate and will get progressively out of sync if people are
> banging away at it. So you would loose consistency as you do it.
> However, my approach will "break" the table as you are making the changes so
> the app which is using this column will fail as you do your renaming.
> So neither solutions are perfect - mine takes longer but might guarantee
> better consistency than yours. Your method is shorter, but your method is
> preferred if you can kick all of your users off your system.
> --
> 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
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> OldColumn
> AM?
> applied
> same
> column
> column
> the
> the
> wish
> name
> temp
> to make
> named:
> SQL
> rename
> Column
>
>

Rename a column

Is it safe to use DBCC RENAMECOLUMN(@.table, @.oldname, @.newname)
or is better to drop the column and recreate it?
Thanks in advance?DBCC RENAMECOLUMN is undocumented so the answer is, no, it is not
"safe".
In SQL Server 7.0 and 2000 use sp_rename to rename a column. No need to
drop and re-create it.
David Portas
SQL Server MVP
--|||Since that's undocumented, why not try sp_rename:
exec sp_rename 'tbl.ColOldName', 'ColNewName', 'column'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"x-rays" <xrays@.discussions.microsoft.com> wrote in message
news:F190B789-8C8F-4FFE-8581-AE2641792F75@.microsoft.com...
> Is it safe to use DBCC RENAMECOLUMN(@.table, @.oldname, @.newname)
> or is better to drop the column and recreate it?
> Thanks in advance?|||Hi
DBCC RENAMECOLUMN is not documented, so don't use it.
Rather use sp_rename.
Regards
Mike
"x-rays" wrote:

> Is it safe to use DBCC RENAMECOLUMN(@.table, @.oldname, @.newname)
> or is better to drop the column and recreate it?
> Thanks in advance?|||Yeah, sure I can use sp_rename, thank you all replying.
"x-rays" wrote:

> Is it safe to use DBCC RENAMECOLUMN(@.table, @.oldname, @.newname)
> or is better to drop the column and recreate it?
> Thanks in advance?|||You should be very careful when renaming columns as you could break existing
views, stored procedures and user defined functions if any of these objects
use the column.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"x-rays" wrote:
> Yeah, sure I can use sp_rename, thank you all replying.
> "x-rays" wrote:
>

Removing Zeros

Hi,
I have a column cost with sample record as '00003433' . how can i remove all zeros in left side alone?.

Out put : 3433.

Thanks
Raj

You can just convert the value to INT.

e.g.

Code Snippet

select convert(int,'00003433')

|||

Try this..

Select replace(Columnname, '0','')

From table

*** Place your tablename and column name in the query..

|||

Hi,

Oj is correct, use convert function to removes all zeros in left side of your column data, but when you use "repalce" function it'll removes all zeros, not only in left side.

for eg:

declare @.a varchar(8)
set @.a = '00003405'

select convert(int,@.a) ,replace(@.a, '0','')

Thanks & Regards,

Kiran.Y

Removing white spaces in a varchar column

I have a table . It has a nullable column called AccountNumber, which
is of varchar type. The AccountNumber is alpha-numeric. I want to take
data from this table and process it for my application. Before doing
that I would like to filter out duplicate AccountNumbers. I get most of
the duplicates filtered out by using this query:

select * from customers
where AccountNumber NOT IN (select AccountNumber from customers where
AccountNumber <> '' group by AccountNumber having count(AccountNumber)
> 1)

But there are few duplicate entries where the actual AccountNumber is
same, but there is a trailing space in first one, and hence this
duplicate records are not getting filtered out. e.g
"abc123<white-space>" and "abc123" are considered two different entries
by above query.

I ran a query like :

update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)

But even after this query, the trailing space remains, and I am not
able to filter out those entries.

Am I missing anything here? Can somebody help me in making sure I
filter out all duplicate entries ?

Thanks,
RadAre you sure it's a whitespace? It might be a line break. To verify,
you might try something like:

SELECT AccountNumber
FROM customers
WHERE AccountNumber LIKE '%" + CHAR(10)
OR AccountNumber LIKE '%' + CHAR(13)

Oh, and always be cautious about running an UPDATE statement with no
WHERE clause.

HTH,
Stu|||Thanks Stu, you made my day. :)

It was a line break after a white space. I was concentrating only on
the white space and did not notice the line break. So in my first
statement, I first use this query:

UPDATE customers
SET AccountNumber = substring(AccountNumber, 1, PATINDEX('CHAR(13)',
AccountNumber))
WHERE AccountNumber like '%' + CHAR(13)

and it worked!

Thanks again!

Regards,
Rad

Stu wrote:
> Are you sure it's a whitespace? It might be a line break. To verify,
> you might try something like:
> SELECT AccountNumber
> FROM customers
> WHERE AccountNumber LIKE '%" + CHAR(10)
> OR AccountNumber LIKE '%' + CHAR(13)
> Oh, and always be cautious about running an UPDATE statement with no
> WHERE clause.
> HTH,
> Stu|||>> table . It has a nullable column called AccountNumber, which is of VARCHAR(n) type. <<

That is a very bad code design because it prevents check digits and
makes validation rules more complex. If you had validation rules in the
DDL, you would not have this problem. First mop the floor, then fix
the leak.

Removing spaces within a string

I want to remove spaces within a string in a column if there is more than 1
space between 2 words
For ex
ROYAL SCOTLAND --> ROYAL SCOTLAND
AMERICAN EXPRESS --> AMERICAN EXPRESS
DELOITE TOUCHE --> DELOITE TOUCHE
Does anyone have any creative ways of doing the above
Thanks
Nice thing, already discussed in here:
http://groups.google.de/groups?hl=de...gbl%26rnum%3D1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Sanjay" <Sanjay@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B403551F-BD26-4502-9833-51A7A19C819D@.microsoft.com...
>I want to remove spaces within a string in a column if there is more than 1
> space between 2 words
> For ex
> ROYAL SCOTLAND --> ROYAL SCOTLAND
> AMERICAN EXPRESS --> AMERICAN EXPRESS
> DELOITE TOUCHE --> DELOITE TOUCHE
> Does anyone have any creative ways of doing the above
> Thanks

Removing spaces within a string

I want to remove spaces within a string in a column if there is more than 1
space between 2 words
For ex
ROYAL SCOTLAND --> ROYAL SCOTLAND
AMERICAN EXPRESS --> AMERICAN EXPRESS
DELOITE TOUCHE --> DELOITE TOUCHE
Does anyone have any creative ways of doing the above
ThanksNice thing, already discussed in here:
hx.gbl&rnum=1&prev=/ groups%3Fq%3Dblanks%26hl%3Dde%26lr%3D%26
group%3Dmicrosoft.publ
ic.sqlserver.programming%26selm%3DunisU2McEHA.3580%2540TK2MSFTNGP11.phx.gbl%26rnum
%3D1" target="_blank">http://groups.google.de/groups?hl=d...num
%3D1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Sanjay" <Sanjay@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B403551F-BD26-4502-9833-51A7A19C819D@.microsoft.com...
>I want to remove spaces within a string in a column if there is more than 1
> space between 2 words
> For ex
> ROYAL SCOTLAND --> ROYAL SCOTLAND
> AMERICAN EXPRESS --> AMERICAN EXPRESS
> DELOITE TOUCHE --> DELOITE TOUCHE
> Does anyone have any creative ways of doing the above
> Thanks