Removing apostrophe from multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Removing apostrophe from multiple tables

Author

Topic

jayram
Starting Member

47 Posts

Posted - 2012-09-24 : 16:32:17

Hello,

I have around 50 tables in a database where a few of them have a column called NAME and i want to remove apostrophe from the NAME column in all these tables

i can do it individually by replace(NAME,'''','')
but is there Dynamic SQL method to look up all Table thah have NAME column and generate an Update statement.

in the past i have used this in order to round variables in a table

SELECT 'UPDATE TABLE SET '
UNION ALL
SELECT quotename(column_name) + '=ROUND(convert(numeric(18,4),' + quotename(column_name) + '), 3), '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='DRUG' AND DATA_TYPE='FLOAT'

Is there something similar for apostrophes?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-24 : 17:01:21

yep.. use something like


EXEC sp_Msforeachtable 'IF  EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Name'') SELECT ''UPDATE ? SET NAME=REPLACE(NAME,'''''''''''''''','''''''')'''

copy and paste it otherwise you will mess with number of quotes

this will form query string for you

choose results as text run above query and copy and paste the result obtained onto a new window to execute the generated query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jayram
Starting Member

47 Posts

Posted - 2012-09-25 : 09:05:28

Thanks Visakh

I did run it but it pulls out an update script for all the tables not just the tables that have a NAME column.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-25 : 09:26:41

quote:
Originally posted by jayram

Thanks Visakh

I did run it but it pulls out an update script for all the tables not just the tables that have a NAME column.

But it will do the update only if there is a column named Name.
See the IF EXISTS...

Too old to Rock'n'Roll too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-25 : 12:54:59

quote:
Originally posted by jayram

Thanks Visakh

I did run it but it pulls out an update script for all the tables not just the tables that have a NAME column.

not true

then that means field name is not exactly Name but it may have some other parts as well

if thats case change logic from = to LIKE using %

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jayram
Starting Member

47 Posts

Posted - 2012-09-26 : 15:32:41

i do this

EXEC sp_Msforeachtable
'IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like ''%Name%''
and (TABLE_NAME like ''%pr%'' or TABLE_NAME like ''%HO%''))
SELECT ''UPDATE ? SET NAME=REPLACE(NAME,'''''''''''''''','''''''')'''

but it still retrieves an update statement for tables without name like pr or ho

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-26 : 17:05:35

It is because the IF EXISTS() has no relation to the actual table in progress by sp_Msforeachtable...

maybe this:

EXEC sp_Msforeachtable 'IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''Name'' and table_name = parsename(''?'',1)) SELECT ''UPDATE ? SET NAME=REPLACE(NAME,'''''''''''''''','''''''')'''

Too old to Rock'n'Roll too young to die.
edit: inserted parsename to get the plain table name...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources