how to replace a particular values in coumn names

Question

how to replace a particular values in coumn names

tripri

SSCommitted

Points: 1584
More actions
March 15, 2011 at 2:44 pm

#236406

hi i got ten column names like #gorge, H#res, Er#es like that now i want to replace all the '#' values with '.' here all that are the column names so i need to modify the column names by replacing # with .

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

tripri (3/15/2011)
hi i got ten column names like #gorge, H#res, Er#es like that now i want to replace all the '#' values with '.' here all that are the column names so i need to modify the column names by replacing # with .

the function sp_rename is what you'd use to rename the column; here's a coding example;

exec sp_rename 'TableName.H#res','Hires','COLUMN'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323482 More actions · Answer 2

ig you know it was a misspelling, where every # should have been the letter i or something, you could script all the commands together, or use this a s a basis and hand edit them before actually running the code:

--exec sp_rename 'blah.h#res','hires','COLUMN'

select 'exec sp_rename ''' + object_name(object_id)+ '.' + name + ''',''' + REPLACE(name,'#','i') + ''',''COLUMN'' ' AS TheCommand

from sys.columns where name like '%#%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

Wow changing columns names from 'H#res' to 'H.res' is going to open an enormous can of nastiness.

things like

select #gorge, H#res, Er#es

will have to become

select [.gorge], [H.res], [Er.es]

Just making sure you know how much editing of code you will have to do if you rename columns containing a '.'

The impact is more than just changing '#' to '.' in every single piece of sql that touches any of those tables. :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

If there was a common naming convention for keys and indexes, this change will wipe out any automation associated with such things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions