SQL Server Search-and-replace Program

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


ADVERTISEMENT

Keyword Search Program

Sep 11, 2003

Hi,

I am in the middle of developing procedure for keyword search for our website.

Input parameter is a string consisting keywords in comma delimited format.

This is the example of data I get from business group which I use to populate my key_search table.

product_id keyword
1 Microsoft, training
1CA, DBA
1CA, developer
1CA, network admin
1AZ, DBA
1AK, developer
1MN, DBA
1MN, developer
1OH, developer
2Microsoft, training
2AZ, DBA
2AZ, developer
2IL, developer
2MN, DBA
2CA, developer
2NY, business analyst
2NY, DBA
2NY, developer
2NY, programmer
3Oracle, training
3finance
4Oracle demo
4logistic
4Oracle Financials
4Financial Analyzer

They have provided search string examples like 'Microsoft, DBA, CA'
or 'CA'
or 'Microsoft, developer' or 'training'

I have script ready to remove comma from the string and store words from the input string in a temporary table.
But this is the easiest part.

The Confusing part now for me is to write the optimal code for retrieving the exact match from the key_search table as there is no limit on how many words can be in the string, it can be 1, 2, 3 or more.

Any suggestions on how should I handle this search?

View 6 Replies View Related

How Do I Program A Search Engine?

Feb 23, 2008

I heard that making a search engine involves PHP programming. I would like to know how to program a secure well developed image search engine for my website. Are there any websites or books you could recommend? I know a little C-sharp, but I think a beginners book in PHP would be better than an advanced or novice one. I understand this may be in the wrong forum but I figured it may involve some MySQL


www.digital-orb.com

View 1 Replies View Related

How Can I Include Full Text Search Service In My Program Setup Silently?

Mar 18, 2007

heloo..

if I want to use full text search in my program, can I include Full Text Search service in my program setup silently in the same way as including SQL Express and .Net Framework to the setup project, Or I have to Install MS Sql Server 2005 on my customer computer?

View 1 Replies View Related

Search & Replace

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

Search And Replace

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

Search And Replace?

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

Search And Replace (sorta)

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

Search All Tables And Replace

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

Search And Replace Within Store Procedures

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

How To Search For And Replace A Value In All Tables Using A Sql Script.

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

REPLACE CARRIAGE RETURN While Search

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

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 View Related

Search & Replace In Multiple Stored Procedures

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

Search And Replace Only Replaces One Char Per Field

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

Search And Replace Only Replaces One Char Per Field

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

Transformation Editor Or SQL Eitor. How To Search And Replace?

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

Simple Text Processing E.g. Regex Search And Replace

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

This Stored Procedure Can Be Used To Search And Replace Substring In The Char, Nchar,

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

Newbie Search/replace Case Style Functionality In Derived Column Expression Syntax

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

Do Sqlexception Breaks The Functionality Of The Program? (program Flow)

May 23, 2007

why we use sql exceptions ...

what the program will do if we caught that exception .. i need some suggestions ... i got this exception(String or binary data would be truncated.
The statement has been terminated.).. will it affect the functionality of the program...

hiow can i avoid this exception..

View 1 Replies View Related

SQL Server 2014 :: Semantic Search Not Finding Keywords Identified By Full-Text Search?

Nov 6, 2014

I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.

For instance, if I put the following string into a FT indexed table

'J9355 - Trastuzumab (Herceptin)'
AND
'J9355 - Trastuzumab emtansine'

The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'

Nor in

'J8999 - Everolimus (Afinitor)'

It did not recognize 'Afinitor' as a keyword.

In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.

why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?

View 0 Replies View Related

Can I Return A Value In A Variable From A SSIS Program Back To C# After The SSIS Program Is Run From C#?

May 21, 2007

Can I return a value in a variable from a SSIS program back to C# after the SSIS program is run from C#?

View 1 Replies View Related

Create Site Search Using Sql Server Full Text Search

Jul 24, 2007

would you use sql server "full text search" feature as your site index?  from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on...
so i thought create site search table, with the columns:
id, text, url
and to write every thing to this table.
but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server...
what do you think? 

View 1 Replies View Related

How Can I Search Throught DOCX (MS Word 2007) Documents By SQL Server 2005 Full Text Search Engine?

Dec 11, 2006

How can I search throught DOCX (MS Word 2007) documents by SQL Server 2005 Full Text Search engine?

Should I something download?

View 6 Replies View Related

Using Sql Server In DB Program

Oct 26, 2006

I am about to build a program that will require the use of DB. In the pass I used Access as my back end, but this time I would like to use Sql Server. I just have a few questions before I begin.

1. After deploying and installing my program, do I need to have sql server installed on users system or can the DB file exist on its own and just use ADO.NET to access the file? (As I done in the pass to access my Access mdb file)

2. If I can deploy a sql file -- can it be done with Sql Server Express?

3. If it can't be done, what db program should I use?

View 3 Replies View Related

Problems With SQL Server 2K And VB Program

Jun 27, 2001

Hi,
My company has recently migrated our Informix database to SQL server 2K. The data is successfully migrated and the client are able to connect to the database. However, when trying to run a VB application to access the data, it keeps on prompting for SQL server login whenever an sql statement is executed. What could be the problem? We are using SQL server 2k on Windows 2000 and on the client Windows 95. Thanks!

View 2 Replies View Related

How To Run A Vb.net Program From SQL Server 2005?

Mar 15, 2008

Hi. First 3 paragraphs are about problem in general - this is for those who consider them well experienced and may have better ideas about how to solve this whole thing. In forth paragraph is the tehnical question.



Hi. First 3 paragraphs are about problem in general - this is for those who consider them well experienced and may have better ideas about how to solve this whole thing. In forth paragraph is the tehnical question.

Situation:
So me was trying to find out why files are saved in database incorrectly. First idea was to create a MD5 checksum of a file in my application and then create one with SQL script that saves data in database (data being the file, by the way).

On software side it worked fine 'cuz i'm developing in Visual Basic .NET 2005 which has a function that return a 32 byte array of hash from a data that you pass to it (like file) - GREAT!

SQL Server 2005 side - not so great. The only function that I have found so far that creates hash is HashBytes that is supposed to do it's job only there's a limintation to data size which means i cant make hash of say 4mb big file.

Problem:
So I figure maybe i could create a vb.net program that return a hash from a data passed to it and call it with SQL script (because hashbytes function doesnt work for me in case you didnt read the first 3 paragraphs)... but I don't know how:(
Any ideas?

View 4 Replies View Related

How To Run A Vb.net Program From SQL Server 2005?

Mar 15, 2008

Hi. First 3 paragraphs are about problem in general - this is for those who consider them well experienced and may have better ideas about how to solve this whole thing. In forth paragraph is the tehnical question.

Situation:
So me was trying to find out why files are saved in database incorrectly. First idea was to create a MD5 checksum of a file in my application and then create one with SQL script that saves data in database (data being the file, by the way).

On software side it worked fine 'cuz i'm developing in Visual Basic .NET 2005 which has a function that return a 32 byte array of hash from a data that you pass to it (like file) - GREAT!

SQL Server 2005 side - not so great. The only function that I have found so far that creates hash is HashBytes that is supposed to do it's job only there's a limintation to data size which means i cant make hash of say 4mb big file.

Problem:
So I figure maybe i could create a vb.net program that return a hash from a data passed to it and call it with SQL script (because hashbytes function doesnt work for me in case you didnt read the first 3 paragraphs)... but I don't know how
Any ideas?

View 14 Replies View Related

SQL Server Cannot Install Files To The Compressed Or Encryted Folder: C:program Filesmicrosoft SQL Server

Mar 5, 2007

Where do I go in my computer to correct this problem so that I can complete the installation?

View 3 Replies View Related

SQL 2000 MS Search: Boolean Search Doesn't Work When Search By Phrase

Aug 9, 2006

I'm just wonder if this is a bug in MS Search or am I doing something wrong.

I have a query below

declare @search_clause varchar(255)

set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'

select * from results

where contains(finding,@search_clause)

I don't get the correct result at all.

If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"

then i get the correct result.

It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?

Anyone know?

Thanks

View 3 Replies View Related

How To Call A VB Program From A Trigger Of SQL Server

Feb 23, 2004

Hi,

I have a VB program to sit somethere. I like to call that program with passing two parameters from a trigger on the SQL Server. The parameters are the contents of the table which the trigger is created on.

Is there any way to achieve that?

Thanks a lot!

Regards,

Kevin Jin

View 1 Replies View Related

Sql Server Bulk Copy Program

Aug 1, 2000

Hi,

I am working on sql server bulk copy program. I am getting data files from
our vendors for shares and stocks. The data files are pipe separated values.
for ex the Ascii file format is

8388182|"ACC consultanats"|"rating for the current financial year"|23

My doubt is i have four columns in my sql server table named stocks.

table structure
---------------
serialno numeric
caption nvarchar(255)
memo1 ntext 16
sno int

In this the third column named memo1 in the data file would be a large
volume data. That is it may be upto one full A4 size page.
One important thing is, data in the third column is not formatted.

Since it is very urgent, let me know what would be the format file for
this type of data file and the bulk copy program utility.

Kindly let me know as early as possible.

Regards
Sivaramakrishnan

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved