Post reply

Rename Column with comma

  • August 8, 2016 at 8:45 am

    #312291

    Hi I have comma in column name. we just import from somewhere and cant change. I want to rename column name from upper case to lowercase and i can do it with gui but i have lot and want to use script. and this is giving me error. any thought?

    exec sp_rename '[table_name].[Rec'd 2007]', '[rec'd 2007]', 'COLUMN';

  • August 8, 2016 at 8:47 am

    #1894358

    any object name with a single quote in it needs to be escaped in a command:

    exec sp_rename '[table_name].[Rec''d 2007]', '[rec''d 2007]', '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!

  • August 8, 2016 at 8:58 am

    #1894364

    Thanks

    I was using this to change all at once,

    SELECT

    'exec sp_rename ''' + QUOTENAME( Table_Name) + '.' + QUOTENAME(COLUMN_NAME) +

    ''', ''' + QUOTENAME(LOWER(COLUMN_NAME)) + ''', ' + '''COLUMN'';'

    FROM

    Information_Schema.[COLUMNS]

    Now I need to handle it automatically and when i try to replace it, again error.

    Select column_name, REPLACE(column_name, ''', ''''')

    FROM

    Information_Schema.[COLUMNS]

    Where column_name like '%''%'

  • August 8, 2016 at 9:00 am

    #1894366

    What are the exact errors you're getting?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 8, 2016 at 9:01 am

    #1894367

    Anyway Thanks a lot. I hope this is flawless

    SELECT

    'exec sp_rename ''' + QUOTENAME( Table_Name) + '.' + QUOTENAME(COLUMN_NAME) +

    ''', ''' + QUOTENAME(LOWER(REPLACE(column_name, '''', ''''''))) + ''', ' + '''COLUMN'';'

    FROM

    Information_Schema.[COLUMNS]

    Where column_name like '%''%'

  • August 8, 2016 at 9:04 am

    #1894368

    i think you want something like this:

    SELECT

    'exec sp_rename ''' + QUOTENAME(Table_Name) + '.' + QUOTENAME(REPLACE(COLUMN_NAME,'''','''''')) +

    ''', ''' + QUOTENAME(LOWER(REPLACE(COLUMN_NAME,'''',''''''))) + ''', ' + '''COLUMN'';'

    FROM

    Information_Schema.[COLUMNS]

    WHERE COLUMN_NAME <> LOWER(COLUMN_NAME) COLLATE Latin1_General_Bin2

    AND COLUMN_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!

  • August 8, 2016 at 9:08 am

    #1894370

    u r right Lowel. Thanks a lot again.

Viewing 7 posts - 1 through 6 (of 6 total)

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