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/

Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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/

Go to Top of Page

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
Go to Top of Page

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...
Go to Top of Page
   

- Advertisement -