Replace function accross entire table

  • Is it possible to apply the replace function to all columns in a table? I have over 350 in a table and need to remove all decimal points / full stops. ? ?

    Many thanks.

  • You could use dynamic SQL together with sys.columns. You could check system_type_id to modify just the related data types.

    However, I don't know why you want to "remove decimal points". Do you have the appropriate data type per column?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • not directly, no.

    each column has to be referenced in the update statement, but you can use the schema tables to generate the SQL statement for you.

    you were a little loose on the details...a decimal point replaced in a varchar is different than a decimal point in a decimal/float datatype...do you mean find and replace a '.', or turna value into an integer?

    what is a full stop that you want to replace? a CrLF?

    here's a simple example to generate an update for every VARCHAR field in a given table:

    declare @vbCrLf CHAR(2)

    SET @vbCrLf=CHAR(13) + CHAR(10)

    SELECT

    max(s3.create_date) AS create_date,

    s3.name As [TheTable],'UPDATE ' + s3.name + ' SET ' +

    stuff(( SELECT ',' + name + ' = REPLACE(' + name + ',''badstring'',''goodstring)' + @vbCrLf

    FROM sys.columns s2

    WHERE OBJECT_NAME(s2.object_id)= s3.name --- must match GROUP BY below

    and TYPE_NAME(s2.system_type_id) = 'varchar'

    ORDER BY s2.column_id

    FOR XML PATH('')

    ),1,1,'') as [TheColumns]

    FROM sys.columns s1

    inner join sys.objects s3 on s1.object_id = s3.object_id

    WHERE s3.type='U'

    GROUP BY s3.name --- without GROUP BY multiple rows are returned

    ORDER BY s3.name

    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:

    That's a much more precise way to describe what I was talking about.

    Nice job!!



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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