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.
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???
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?
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?
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
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
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?
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)
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.
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.
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
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
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?
#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):
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?
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.
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.
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
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
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.
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 ?
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"
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.
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.
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)
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
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?
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
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:
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:
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<<<<
