find and replace - list table, and substr

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 2012 Forums

Transact-SQL (2012)

find and replace - list table, and substr

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-06-30 : 02:40:54

find and search text is "1234-5678" or "1234 5678" . I want to search all tables have this text with lists the tables name. Then find the text pattern have space change to "12345678". How to write a sql query ?

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-06-30 : 07:04:51

Below is the query to get Table,Column for the below input :

--create table #table_name
--( id int identity (1,1) , table_name varchar(100),column_name varchar(100),schema_name varchar(100))

--insert into #table_name
--select TABLE_NAME,column_name,table_schema from [INFORMATION_SCHEMA].[COLUMNS] where ORDINAL_POSITION=1 and data_type like '%varchar%'
declare @i int
declare @j int=1
declare @table varchar(100)
declare @column varchar(100)
declare @schema varchar(100)
declare @query varchar(max)
set @i=1;

while (@i<=(select max(id) from #table_name))
begin

set @table = (select table_name from #table_name where id=@i)
set @column=(select column_name from #table_name where id=@i)
set @schema=(select schema_name from #table_name where id=@i)

set @query= 'select ' + ''''+ '['+ @schema+ '.' + @table + ']' + ''''+ ' ,' + @column + ' from '+ '['+@schema + ']' + '.'+ @table + ' where ' + @column+' like ' + '''%12345678%'''
exec(@query)
set @i = @i +1
end

Once you get the required table and columns you can easily replace the text using REPLACE() function.

Hema Sunder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources