SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Removing apostrophe from multiple tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jayram
Starting Member

47 Posts

Posted - 09/24/2012 :  16:32:17  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 09/24/2012 :  17:01:21  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 09/24/2012 17:06:12
Go to Top of Page

jayram
Starting Member

47 Posts

Posted - 09/25/2012 :  09:05:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 09/25/2012 :  09:26:41  Show Profile  Visit webfred's Homepage  Reply with Quote
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

India
52317 Posts

Posted - 09/25/2012 :  12:54:59  Show Profile  Reply with Quote
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 - 09/26/2012 :  15:32:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 09/26/2012 :  17:05:35  Show Profile  Visit webfred's Homepage  Reply with Quote
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...

Edited by - webfred on 09/27/2012 03:08:27
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000