Search Replace Across Multiple Queries
Oct 4, 2007
Hey guys,
In my DB i have hundreds on queries setup for all different reporting purposes..
We have just changed they way our system handles costing and are moving from a average cost function to a standard cost function.. This in turns, means that i need to replace any reports where i use the average cost field and replace it with the standard cost field.
Is it possible, that i can do a full search and replace over all my queries, looking for a specific field name and replacing it with something else.
I am dreading the idea of opening each individual queries and checking if it exists..
If anyone knows of software or someway to write a SP to accomadate this, please let me know. I would be most grateful.
Thanks
Scotty
View 3 Replies
ADVERTISEMENT
Aug 31, 2007
Hi all,
Does anyone know how to do the search and replace in multiple stored procedures using SQL 2005 Management Studio?
Any help will be appreciated.
Thanks!
View 3 Replies
View Related
Sep 23, 2007
Hi,
I am having a situation where I need to update a column in my SQL table that contains a link to an image file. Basically ...
I have this stored in a column IMAGESRC
Project/aa11be5d-dd9e-48c8-9d8c-6a972e996b28/ProjectImages/702d_2.jpg I need to change it to this
Project/NEWUSERID/ProjectImages/702d_2.jpg
How can I accomplish this in SQL???
thanks in Advance
Dollarjunkie
View 2 Replies
View Related
Aug 4, 2006
i like to write a code that street_name field look up in table one for 'road' and replace it for 'rd'.
any approaches ?
table1
abr description
---- ------------
ln lane
rd road
ave avenue
table2
street_name
-------------
apple road
View 20 Replies
View Related
Feb 1, 2008
i have data in a table which is in format #12345;#22211;#12112; and so on... its a long string.
i want to replace only the numbers using nchar() function.
in above example i want to replace 12345, 22211, 12112 with nchar(12345), nchar(22211), nchar(12112).
i have managed to do this using loop and substring function. but now i have performance issue.
63K records will take many hours to update. i have to do following steps:
1. read record from table.
2. replace numbers with nchar() function.
3. update the same table with new text.
i have to do this using a stored procedure.
i am looking for some quick way to do this. any idea?
View 1 Replies
View Related
Aug 30, 2007
I have a notes table in which I have 10,000 rows. Unfortunately some of the notes are compressed together as one long string. I can fix the issue if I can do a search and replace on ';' and replace with '; ' is there a way to do this?
View 3 Replies
View Related
Jul 6, 2007
I have a question about the best method of going about doing this.
I have a records table which stores visitor information from IISlogs (dbo.records)
I have added IP tables to try and resolve where the visitors are from. They are labeled 'ip4_##' where ## is the first section of the ip.. so if my IP was 24.150.66.80 I would need to look in table ip4_24 and inside there look for 150 in column B and then 66 in column C. From that it will say which country is attached, that way I can update the countrycode in the dbo.records.
I am not sure how to do this search, so any ideas would be great.
Thanks in advance
* Table structre for the ip_## tables are
COLUMN 'b' int (represents the 2nd section of an IP)
COLUMN 'c' int (represents the third section of an IP)
COLUMN 'country' int
View 2 Replies
View Related
Jul 23, 2005
I'm looking for a stored procedure (or query) to search an entiredatabase for a specific string value and replace it with another. I'msure I saw an SP for this a while back by someone, but cannot find itagain. The SP took the search string and replace string as parametersand did the rest. Any ideas where I can find this ?Bear in mind, the idea is that this can be re-used and run on anydatabase, so it would have to find all tables and search through those.TaRyan
View 2 Replies
View Related
Nov 29, 2004
I am developing a complex database-driven application with SQL Server 2000. My database has dozens of stored procedures, and whenever I want to rename a database field, I have to go through my stored procedures, finding where that field is used. This is a laborious and error-prone process, even when I look up depenencies.
Is there an easier way to work stored procedure code – some tool to search/replace the text perhaps?
View 1 Replies
View Related
Apr 21, 2006
I found a search-and-replace program for SQL server that works GREAT, but I have a list of about 200 words to search for, with corresponding replacements. Rather than editing the code below for *each* word and running it 200 separate times, I'd like to iterate through the list, but my MS-SQL programming skills are...light (to say the least). Anyone have any ideas how I can create a list or hash-type variable and use the code to loop through and do the replacements all at once?
/*
*
* Search & Replace
*
* Use Ctrl+Shift+M to replace template values
*
*/
set xact_abort on
begin tran
declare @otxt varchar(1000)
set @otxt = '<string1, text, text to be replaced>'
declare @ntxt varchar(1000)
set @ntxt = '<string2, text, replacing text>'
declare @txtlen int
set @txtlen = len(@otxt)
declare @ptr binary(16)
declare @pos int
declare @id int
declare curs cursor local fast_forward
for
select
id,
textptr(<field_name, sysname, target text field>),
charindex(@otxt, <field_name, sysname, target text field>)-1
from
<table_name, sysname, target table>
where
<field_name, sysname, target text field>
like
'%' + @otxt +'%'
open curs
fetch next from curs into @id, @ptr, @pos
while @@fetch_status = 0
begin
print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)
updatetext <table_name, sysname, target table> .<field_name, sysname, target text field> @ptr @pos @txtlen @ntxt
fetch next from curs into @id, @ptr, @pos
end
close curs
deallocate curs
commit tran
View 1 Replies
View Related
Mar 15, 2007
Hello Everyone,
Is there a way to search all the tables in a database for a value that is found in a specific column? Another problem is that although this specific column will be found in most of the tables in the database, it has a different two or three digit prefix in the column name for each table.
I think the logic will go something like this...
As the script jumps from table to table, it should first look for a column whose name ends with ***ITM. If it does not find a column with ***ITM it should move on to the next table. If it does, then search the ***ITM column for my value. If it does not find my value, then move to the next table. But if it does, change my value to a new value.
Any help will be greatly appreciated.
Thank you all...
View 3 Replies
View Related
Oct 26, 2007
Hi All,
I am experiencing problem to select text wich has carriage return in my search functionality.
I have two tables called @searchwordTable and @DataTable.
@searchWordTable will have search criteria words(data type is varchar) and @DataTable will titles(data type is ntext) need to be searched. Some of titles have carriage return,line feed and tab characters. I am preseting here script to reproduce my problem.
DECLARE @searchwordTable TABLE
(
searchword VARCHAR(MAX)
)
INSERT INTO @searchwordTable (searchword) VALUES('carriage long description')
DECLARE @DataTable TABLE
(
title ntext
)
INSERT INTO @DataTable (title) values('carriage long description'+char(13)+char(10)+'carriagelong')
SELECT * FROM @DataTable dTable,@searchwordTable srcWrdTable
WHERE '% ' + REPLACE(CAST(dTable.title AS VARCHAR(MAX)),char(13),'') + ' %' like '% ' + srcWrdTable.searchword + ' %'
I am expecting the above select statement should select title from @DataTable but not getting .I am not understanding what is going wrong with above select.
Thanks in Advance.
View 2 Replies
View Related
Sep 22, 2006
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script
DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)
SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'
SELECT @patfind = '%' + @find + '%'
UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind
View 1 Replies
View Related
Sep 26, 2006
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script
DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)
SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'
SELECT @patfind = '%' + @find + '%'
UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind
View 4 Replies
View Related
Mar 8, 2007
Hi SSIS Gurus,
Coild anybody halp me, how to achive search and replace in the Expressions (Transformation Editor) or in SQL Query box?
If this functionality isn't presented, what is preffered woraround to achieve it?
I have a lot of transformations (about 100) in the derived column task and its too difficult to find transformation what i need.
View 1 Replies
View Related
Aug 8, 2006
I've got an nvarchar(max) column that I need to transform with some simple text processing: insert some markup at the very beginning, and insert some markup just before a particular regular expression is matched (or at the end, if no match is found).
Since the SSIS expression language doesn't support anything like this, is a Script Component the only way to go? Does Visual Basic .NET provide regular expression matching?
Thanks!
View 13 Replies
View Related
Jan 9, 2006
#1 This stored procedure can be used to search and replace substring in the char, nchar, varchar and nvarchar columns in all tables in the current database. You should pass the text value to search and the text value to replace. So, to replace all char, nchar, varchar and nvarchar columns which contain the substring 'John' with the substring 'Bill', you can use the following (in comparison with the SetTbColValues stored procedure, this stored procedure replace only substring, not the entire column's value):
EXEC replace_substring @search_value = 'John', @replace_value = 'Bill'
View 2 Replies
View Related
Dec 5, 2007
I have a number of complex search stored procedures that use the following syntax to try to simplify the code.
WHERE @SearchParam IS NULL OR SearchCol = @SearchParam
unfortunately it appears that this is really inefficient as far as the database is concerned.
If I run the following query on the AdventureWorks database (SQL Server 2005 with SP2 and fixes up to v3054)
Code Block
SET STATISTICS IO ON
Declare @CustomerID int
SET @CustomerID = 1
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE @CustomerID IS NULL OR CustomerID = @CustomerID
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
Is see that the first select results in 45 logical reads, whereas the second results in only 2 logical reads.
Does anyone have any idea how I can get the benefit of a search procedure that does not have loads of IF blocks, or dynamic SQL without this major performance issue?
Cheers,
Justin
View 3 Replies
View Related
Feb 6, 2008
Hi guys,
I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.
similar to this SQL case statement
CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,
the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.
thanks for any assistance you can provide.
regards,
Chris
View 1 Replies
View Related
Mar 23, 2007
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is:
I don't want only the records with columns that contains both searchterms.
I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns.
Jobdescr_________________________|Jobtext
Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP
Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms
I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr
FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1
INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid
INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId
It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
Does anyone know a better solution?
Thanks in advance Bart Rouw
View 2 Replies
View Related
Aug 16, 2007
Guys ... got a puzzle kinda problem am stuck up with so asking for your help (after all .. you are the gurus)... What i need to do is append Src. to any column name that comes in the expression. Here we go ... and the last query is part of what I was coming up with ... not exactly working :) Create Table ColumnNames(ColumnName varchar(256))goInsert into ColumnNamesSelect 'name'UNIONSelect 'dbid'UNIONSelect 'sid'UNIONSelect 'mode'UNIONSelect 'status'UNIONSelect 'status2'UNIONSelect 'crdate'UNIONSelect 'reserved'UNIONSelect 'category'UNIONSelect 'cmptlevel'UNIONSelect 'filename'UNIONSelect 'version'goDeclare @Expression varchar(256)Select @Expression = 'dbid = 1 AND cmptlevel = 100' Select replace(@Expression,ColumnName,'Src.'+ColumnName) from ColumnNames Where charindex('Src.',replace(@Expression,ColumnName,'S rc.'+ColumnName)) <> 0
View 4 Replies
View Related
Sep 30, 2006
I have a query below that performs horribly:@KeywordOne char(6),@KeywordTwo char(6),@KeywordThree char(6),@KeywordFour char(6),@KeywordFive char(6)SELECTc.SomethingFROMdbo.tblStuff cWHEREc.SomeColumnName = 0AND (c.Keyword LIKE '%' + @KeywordOne + '%' OR @KeywordOne is Null)AND (c.Keyword LIKE '%' + @KeywordTwo + '%' OR @KeywordTwo is Null)AND (c.Keyword LIKE '%' + @KeywordThree + '%' OR @KeywordThree isNull)AND (c.Keyword LIKE '%' + @KeywordFour + '%' OR @KeywordFour = isNull)AND (c.Keyword LIKE '%' + @KeywordFive + '%' OR @KeywordFive = isNull)The contents of column c.Keyword looks like this:Row1: 123456,321654,987987,345987Row2:Row3: 123456,987987etc.What can I do to get this to perform reasonably? I cannot use full-textsearch.Any help is appreciated.lq
View 4 Replies
View Related
Oct 12, 2015
I have a table of raw data where each column can be null. The thought was to create an identity key (1,1) and set as primary for each row. (name/ address / zip/country/joindate/spending) with surrogate key: "pkid".However other queries will not use this primary key. So for instance they may count the # of folks at a zip, select all names, addresses etc. The queries may order by join date, or select all the people that joined on a specific date.No other code would logically use the primary key (surrogate primary id key), therefore would it still have any performance benefits? at this time the table would have no clustured or nonclustured indexes or keys. I'm curious if there are millions of records.
View 7 Replies
View Related
Dec 21, 2007
Hi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
@name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote
1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25
Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?
Thanks in advanced
View 4 Replies
View Related
May 19, 2004
I have several fileds in a sql table which holds some text and a hell of a lot of white spaces (char(32)) which is causing some problems when I show the data on the front-end
What I want to do is use sql replace function to replace the char(32). However, because there is text I can't do a simple replace as the text will become just one word !
Can anyone tell me how to loop through the field to find the char(32), where the char(32) is greater than say 5 sapces and then replace this with just one single space
E.g. a field in a table contains the following :
"my text field and loads of spaces then some more text with loads of spaces "
I want to replace the char(32) with just one space so it looks like this :
"my text filed and loads of spaces then some more text with loads of spaces"
Thanks for your help
View 3 Replies
View Related
Feb 15, 2007
I need to map several columns of data from one database to anotherwhere the data contains multiple spaces (once occurance of a variablenumber or spaces) that I need to replace with a single space. Whatwould be the most efficient way to do this? I am using SQL2K. I wasthinking a function since I know of no single Transact-SQL commandthat can accomplish this task.
View 8 Replies
View Related
Sep 10, 2015
I have several fields that have multiple spaces between the City State and Zip Code. I want to be able to make only one space between each. A combination of Substring and Replace is what i have been trying but not able to make it work. a do while might be what i need but not sure how to do it.
View 10 Replies
View Related
Jun 20, 2007
I need to pass 3 column values and one Formula string into 4 replace statements and output the result in one column.
Nesting them in the usual way doesn't seem to work as that only allows for one column.
My table consits of four columns...PF (numeric), Hours (numeric), TotalNumber INT, and Formula (nvatchar)
My function needs to search and replace the Formula column for instances of all the three number columns and output the formula as a mathmatical formula rather than a string.
Here is what I have so far which works fine if all three columns have a value, but if only one is null then it will retrun NULL and not the other two values.
FUNCTION GetFormula
(@numPF NUMERIC(10,2), @numHours NUMERIC(10,2), @intTotalNumber INT, @strFormula nvarChar(200)) RETURNS nvarchar(200)
AS
BEGIN
DECLARE @strExpression nvarchar(200)
SELECT @strExpression=REPLACE(@strFormula, 'TotalNumber',@intTotalNumber)
SELECT @strExpression=REPLACE(@strExpression, 'PF',@numPF )
SELECT @strExpression=REPLACE(@strExpression, 'Hours',@numHours )
RETURN @strExpression
END
Many Thanks
View 3 Replies
View Related
Jul 13, 2015
I need one query:
create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
[Code] ....
Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name
select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.
create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
[Code] ....
View 5 Replies
View Related
Jul 9, 2007
I'm would like to replace all occurrences of "99999" and "-99999" with "" in a column using SSIS. I can use the REPLACE function in a Derived Column to replace one of those strings, for example: REPLACE(mycolumn,"99999",""). Or to replace both I could use REPLACE(REPLACE(mycolumn,"-99999",""),"99999",""). This seems kind of cumbersome and would get very complicated if I were replacing more strings with "". I'm guessing there is a better way. Can anyone help me out?
Thanks,
Ridium
View 12 Replies
View Related
Jul 13, 2015
I need one query...
create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
-- select * from #task
[Code] ....
Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name
select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.
create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
(
with data as
[Code] ....
View 7 Replies
View Related
Mar 4, 2008
Running into a problem with trying to pull multiple values in a stored procedure.
Ran Profiler for trace on what variable is coming back as when running in Reporting Services.
Profiler shows this:
exec apGetCompanysByRep @Product = N'Absorbent Pads,Airedale Terrier'
Trying to run Replace function but not quite getting the syntaxt correct. The stored procedure will work but when running report will often get unclosed quotation marks.
What I have:
Set @Product=
Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + ''''))
This is probably a little too much coding but I've tried several ways.
In the SP the code is as follows:
CREATE PROCEDURE [dbo].[apGetCompanysByRep]
@Magazine varchar(6) = null
, @DirectsMagazine varchar(6) = null
, @Category varchar(50) = null
, @SubCategory varchar(50) = null
--, @FirstName varchar(30)
, @LastName varchar(30)= null
, @Product varchar(1100)
AS
declare @SQL varchar(2000)
Set @Product=Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + ''''))
set @SQL = '
Thanks for any assistance anyone can give.
View 9 Replies
View Related
Jun 21, 2012
I am trying to replace all special characters in a column with one special character.
Example:
Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"
I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.
I am expecting the following results:
Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<
View 9 Replies
View Related
