Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replace function accross entire table Expand / Collapse
Author
Message
Posted Sunday, September 19, 2010 2:04 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:46 AM
Points: 158, Visits: 504
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.
Post #989004
Posted Sunday, September 19, 2010 3:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 6,842, Visits: 13,372
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #989010
Posted Sunday, September 19, 2010 3:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:41 AM
Points: 12,953, Visits: 32,480
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #989011
Posted Sunday, September 19, 2010 3:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 6,842, Visits: 13,372
@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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #989012
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse