September 19, 2010 at 2:04 pm
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.
September 19, 2010 at 3:02 pm
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?
September 19, 2010 at 3:08 pm
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
September 19, 2010 at 3:16 pm
That's a much more precise way to describe what I was talking about.
Nice job!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply