Replace One Word With Another In Text Field

Oct 19, 1999

I have a text field that contains multiple words. Is there a way with SQL to relace a single word with another?

for example:

FIELD : CompanyName
DATA : Microsoft Corp.
DATA : IBM Corp.
etc...
DATA : Canon Corp. of America

How can I run a routine to just REPLACE "Corp." with "Corporation" ?

Thanks for your help!
Scott

View 1 Replies


ADVERTISEMENT

Split Text Field - Display 1st Or 2nd Word

Sep 24, 2012

In SQL SErver 2008, I have a text column. I need to display either 2nd word in the text column or 1st word in the text column based on certain conditions.

How shall i display either 2nd word or 1st word from a text field.

View 1 Replies View Related

Counting Of Occurrences Of A Word In A Text Field

Oct 27, 2003

Finding numbers of occurrences of a string of characters
in a column of TEXT datatype.
DDL of involved table txt:
create table txt (pk int, txtcol text) -- datatype of pk doesn't matter

declare @word varchar(80) set @word='help'
declare @pk int, @count int, @i int, @dl int, @wl int
set @wl=len(@word)
declare abc cursor for select pk from txt
where patindex('%'+@word+'%',txtcol)>0 order by pk
open abc fetch next from abc into @pk
while @@fetch_status=0
begin
select @dl=datalength(txtcol) from txt where pk=@pk
select @i=patindex('%'+@word+'%',txtcol)+@wl from txt where pk=@pk
set @count=1
while @i<@dl
begin
select @count=@count+(len(substring(txtcol,@i,8000))-
len(replace(substring(txtcol,@i,8000),@word,'')))/@wl
from txt where pk=@pk
set @i=@i+8001-@wl
end
select pk=@pk, occurrences=@count
fetch next from abc into @pk
end
close abc deallocate abc
pk occurrences
----------- -----------
1 1

pk occurrences
----------- -----------
2 2

pk occurrences
----------- -----------
3 11
Edit: as suggested-reminded by jsmith8858.

View 7 Replies View Related

Replace Text In Text, Char && Varchar Fields All At Once?

Jan 22, 2008

I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.

View 9 Replies View Related

Changing Word Doc Text Using T-SQL

Sep 13, 2007



Hi,
i was checking books online and run into the sp_OAxxxx stored procs that can give us access to a OLE Object.
I am currently developing a VB.Net app that uses a word template to create word documents. So, for example ,i we suppose that the template is :
-----Template start -------
This document was created by <!_author_> on <!_date_>
-----Template end --------
then, the created document would be :
-----Created doc start -------
This document was created by justsarter on 09/13/2007
-----Created doc end --------

All the data are fetched from a database and i have to make muptiple calls to it ,not to mention opening and closing word.application object.

I wonder if that kind of text manipulation in the document can be performed on the server using an SP
Thx
theodore

View 1 Replies View Related

Word Doc In Image Field Type

Feb 25, 2005

Hi,

I was wondering if someone can help me with is problem.

I have uploaded word docs to the db which is fine. The problem is viewing. I can view then as word documents but the boss does not want the files opened in word.

Is it possable to retrieve the file from from the db and put into say a textbox or lable.

I can see the letter P using this code

Dim Doc() As Byte = New Byte(Convert.ToInt32(0)) {}
Dim bytesReceived As Long = DBContent.GetBytes(0, 0, Doc, 0, Doc.Length)

Dim encoding As ASCIIEncoding = New ASCIIEncoding
lblTest.Text = encoding.GetString(Doc, 0, Convert.ToInt32(bytesReceived)).ToString

Can any help

Thanks

View 4 Replies View Related

Adding A Word To Existing Value Of A Field

Sep 17, 2007

Hi!
I want to add a word to a value if the value already exists in that field. How to do this? Please help me. In detail, i have 'id', 'name' and 'info' three columns in one Data Table. When I inserted one value to id field, if the value already exists it should add a word to that value and it should get inserted. Please help me to do this?
Thanks in advance!

View 5 Replies View Related

Searching For A Word On Each Table On Very Field

Feb 9, 2006

Hi everybody,

is it possible using SQL Server search for a word on each
table on each field?

Thanks

Fabio

View 6 Replies View Related

Criteria; Search Any Word In Field

May 26, 2004

I am trying to set up a query that will allow the user to input a string, and the search will match ANY word in that string. Currently, I have it configured so that the search will only match the exact string that the user inputs. I have google searched for the answer, but no luck yet. Any ideas?

View 9 Replies View Related

How To Search Text In A Word Document ?

Nov 24, 2005

I'm designing a Job Recruitment Website, in which the admin person searches for the right candidate for the job using certain keywords . Each jobseeker will be uploading  his CV (ms word doc) during registration .How can i search for keywords in  the word documents. I just want the candidate reference once i found keyword match in the word docs.I heard about the indexing and blobs in sql server? But dont know much about it Are these the only solutions ?Is there any better approach for this ?Any help will be greatly appreciated

View 4 Replies View Related

Find Exact Word In Full-Text Search

Oct 3, 2007

Hi,
I have the fields like this.

Session1               Session2                  Session3   Session4----------------------------------------------------------------------------------------- SQL Server-Part1    SQL Server-Part2      ASP.Net    CSS
C#                         SQL Server-Part3    ASP.Net    Javascript 
I have set the Full-Text to all the columns. For searching i wrote the below query
SELECT * FROM <TABLE NAME> WHERE CONTAINS(*,'"SQL Server-Part1"') 
My Result expectation is: The  First Record should come. But the result of the query bring the two records. It see the "SQL Server" string also. I need to find the exact word. How to do it? Please answer me as soon as possible. Ganesh. 
 

View 6 Replies View Related

How To Compare Two Word Documents Using Full Text Search?

Mar 19, 2006

can we use full text search and mining algorithms to comapre two word or text documents to find out if they are similar
please help.
thaks for reading

View 3 Replies View Related

SQL 2012 :: How To Count Instances Of Each Word Throughout Comments Field Across The Table

Oct 21, 2015

I have to count the number of instances of each word in the Comments column throughout the table using SQL Server. Eg:

Row# Comments
1 I like working on SQL
2 I enjoy sleeping and like watching TV

So the output should be :

Word Count
I 2
like 2
working 1
on 1
SQL 1
.......

Any way to get this count using MS SQL?

View 5 Replies View Related

How To Run Replace On Varbinary Field

Dec 31, 2013

I need to run a replace on a varbinary field but I am unsure how. I have tried mulitple different methods and to no avail. I am stumped.

I have attached a zip folder that contains a spreadsheet that shows what I am trying to do. In the first tab it shows the SQL command I use to call the field I want to change. In this case it's the Content field and I am calling it using the content_key. As you can tell the content is stored as varbinary. In the second sheet, It shows this varbinary being converted to varchar(max) and it shows what the actual text says.

The table that this information is stored is is called digitalassetcontent

I need to remove the

Code:
<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>

That is in front of the actual product description.

Somehow I need to run this command:

Code:
Update digitalassetcontent
set content = replace(CAST(content as varchar(max)), '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>','')

Where CAST(content as varchar(max)) like '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>%' AND content_key = 'desc214974236480438500781058983745755010'

This is the error message I get when running this code: Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

On the varbinary field. How do I do this?

View 4 Replies View Related

Replace Field Value Using Wildcard?

Feb 23, 2014

Is it possible to replace a string value that uses a wildcard? For example, I'm trying to update a field on a table and replace the field value where it starts with a specific two character pattern and remove it from the string. The field is a delimited string and I want to replace any occurrence of ;A0% or ;A0* with a null value. I don't want to replace the entire field with a null value.

Let's say my field value is this:

;00236;08231;06106 Washington DC;06106 CCA Wash DC;05120;A0106;

I want the result to be this:

;00236;08231;06106 Washington DC;06106 CCA Wash DC;05120;

I was trying several variations:

UPDATE myTable SET myField = REPLACE(myField,'XX%','')

View 1 Replies View Related

In A Real Pinch With FreeTextBox / Paste From Word / Ntext Field In SQL2k!

Apr 10, 2005

I am in a real squeeze here.  I am working on my first ASP.NET project here and I am having some big problems with a content management aspect of this so far.  I have a freetextbox control that my users want to be able to paste out of Word and save the content to a MSSQL Database.  Well this was working great until I found out that I have a 4000 character limitation, and most of the pasted data is well over 14000 characters at least.
I've been working on these for about two weeks and just have come to a dead end.  Here's what I had before:  Form with Freetextbox that saved the information in the form to a database by calling a function in a class.vb file, the class file then called a stored procedure using parameters.  Similiarly the display page loaded with a record id, and fired off a function stored in a class file, using stored procedures and using output parameters to I could assign the returned values to label fields etc.  This has completely gone down the toilet since i had to change to text field to accomodate the larger text sizes.  I have found some resources about chunking out to the file system, but I really don't want to do that, I just want it to display the information out on a web page, and I have been scouring google for weeks!  Can someone tell me if this the wrong approach?  How do I go about storing / retrieving HTML in an ntext database field just like these forums?  I am completely stumped!

View 2 Replies View Related

Replace Varchar Text

Dec 11, 2007

Hello Guys.

Here is my issue i have email addresses in a column of a table in sql server. Are addreses has changed since and i need to do a mass update of these email addreses i need to replace a few char to reflect this change like ex:

AAAAA@BBB.CCC.Com i need to replace the BBB. part with nothing so that it looks like this AAAAA@CCC.Com.

Please help.

View 4 Replies View Related

Replace Some Text From My Code

Aug 25, 2006

sunil writes "hi,
i have a problem when i updating record in the database. i am trying to insert value ('Company's Director') in the field but i receive an error Incorrect syntax near 's' what do i do for this error.i want to value like as ('Company''s Director').
please solve my problem"

View 1 Replies View Related

Transact SQL :: Return Field When A Field Contains Text From Another Field

Aug 25, 2015

I'm new to SQL and I'm trying to write a statement to satisfy the following:

If [Field1] contains text from [Field2] then return [Field3] as [Field4].

I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.

View 8 Replies View Related

Searching For A Word In A Text File And Retuning It Using Script Taks In Ssis

May 10, 2007

does any one how to search for a word in a text file and return it back using the script task in ssis?



the file may contain data like this



POSITION SMSMSS20051230000

S ,,751600 ,,20051110,,20051230,20051230

S ,,751600 ,,20051110,,20051230,20051230

S ,,751600 ,,20051110,,20051230,20051230

S ,,751600 ,,20051110,,20051230,20051230

S ,,751600 ,,20051110,,20051230,20051230



what i am looking for is to be able to parse and get the date which is present in the first line "POSITION SMSMSS20051230000" as "20051230"

and then return that as a variable ..



Thanks for any help in advance



smathew







View 3 Replies View Related

How To Replace Char In Ntext Field

Jun 16, 2006

I need help on replace char in ntext data type

Here is the example data
<qMultipleChoice><qText>The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off
20on.</qText><qChoice>Strongly20Disagree</qChoice><qChoice>Disagree
</qChoice><qChoice>Agree</qChoice><qChoice>Strongly%20Agree</qCh

I want result look like this
First Column:The AE Understands what conditions the Account Manager is allowed to sign-off.
Second Column: Strongly Disagree Disagree Agree Strongly Agree

This is what i had so far
Select (SUBSTRING(QuestionText, (PATINDEX(N'%<qText>%', QuestionText) + 7),(PATINDEX(N'%</qText>%', QuestionText) - (PATINDEX(N'%<qText>%', QuestionText) + 7)))) From tblQuestion

my result:
The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off%20on.

I have problem with replace '%20' and how to make the second column.
Any Help?
Thanks
Shan

View 1 Replies View Related

Replace*** In Image Path Field.

Nov 21, 2006

I am hoping someone will be able to help me with this.
i have a table of accountholders with the following fields
account_no
account_name
image_path
the image_path field has a defaultvalue images/***.jpg
I am looking for a query code to update the value in the image_path field by replacing the *** value with the value of the

account_no field

View 1 Replies View Related

Replace Chars In Any Field Of A Table

Feb 1, 2006

I need to strip some puntcuation from any field in a given table.I'd rather like to avoid using the replace () for each field in thetable.Anyone have a nifty way do this?Is there a special name that I can use in the replace that means theentire row?(other than syntax, something like REPLACE(@ROW,CHAR(39),'') )tiaRob

View 1 Replies View Related

Replace A Character Inside A Field

Mar 24, 2008

Hi, I need to replace a character from a column of a txt file. I have defined the column and the values (datetime type for sql server) that I receive are like this: 2008-02-15-20.07.19
So, I need to replace the "." with a ":" beacuse those are minutes and sql server uses ":" for minutes
How can I do this? Any help?
Thanks

View 9 Replies View Related

Replace Text (sorry For The Double Post)

Aug 16, 2001

ok Im sure this is simple. what is the command to execute a replace in a select statement

SELECT CUSTOMER.customer_id, CUSTOMER.full_name, CUSTOMER.main_address_1, CUSTOMER.main_address_2
FROM CUSTOMER
WHERE (((CUSTOMER.main_address_1) Like '%road%'))

???Replace all instences of road with RD???
can some one help on this one or even a refrence for research (besides BOL or Technet)

thanks for the help

matt

View 2 Replies View Related

While Loop To Replace Text - How To Use Set Processing

Aug 12, 2015

I’ve created a script which will do the following.

Update fields in a database which contain instances of an order number. An order number is defined as a 10 digit numeric sting which beings with 998. The first 3 digits of the order number need to change from 998 to 999. There are two types of fields to update: document number fields which may contain 1 instance of the order number and free text fields which may contain multiple instances of an order number.

I created a function which accepts the text to be updated, the text to find and the text to replace it with. The function then loops through the sting for instances of 998. For each instance it finds it checks to ensure that it is at the start of a 10 digit string which contains only numeric numbers – if this is the case then it will update the 998 to be 999.
If the field is free text then it will continue to loop through the string (it will skip forward 10 digits for every order number found) until the end. If the field is not free text then it will exit the script after the first instance found which matches the above criteria, if any.

Need achieving this via set processing and not having to loop through every line. I’ve included the function below and some test data.

--Create the function we will call in order to do the replace
if object_id(N'OrderReplace',N'FN') is not null
drop function dbo.OrderReplace;
go
create function dbo.OrderReplace (
@Textnvarchar(4000),

[code]....

View 8 Replies View Related

REPLACE Integers With Text Data

Jun 14, 2006

I am working with a database named €œDocuments€? that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:

1 = Text
2 = Report
3 = Background
4 = Index

I would like to create a new column named €œDocType€? in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I). I was able to easily create the new column and cast the data type from integer to varchar:

--CREATE NEW COLUMN €œDocType€? WITH VARCHAR DATATYPE

ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL
Go

--UPDATE NEW COLUMN WITH CAST STRING

UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))
Go

But I have problems with the REPLACE method for replacing the numbers with letters. First I tried this based on the examples in MSDN Library:

--REPLACE NUMBERS WITH LETTERS

UPDATE Fulldocuments REPLACE (DocType,"1","T")

Which produced an error message: €œIncorrect syntax near 'REPLACE'.€?

Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:

UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")

Which produced the same error message: €œIncorrect syntax near 'REPLACE'.€?

I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.

View 3 Replies View Related

How To Replace Spacebar In Text With Condition

Mar 20, 2008

need help with spacebar
i have table with names i need to replace the spacebar with "-"
only where val=2
my table

before

fname val
-------------------------------------------
aaaa bbbbbb 1
xx oihjhjhjh 2
sspppp pppll 2
ooooooo ne 1
xxoihjhjh jhkkhhk 2


after only where val=2

fname val
-------------------------------------------
aaaa bbbbbb 1
xx-oihjhjhjh 2
sspppp-pppll 2
ooooooo ne 1
xxoihjhjh-jhkkhhk 2



TNX

View 11 Replies View Related

Replace A String In An NTEXT Field In Sql Server

Oct 15, 2006

I found it rather hard to replace a string in an NTEXT field in sql server 2000. Would it be easier in SSIS 2005? Please advise. Thanks.

View 8 Replies View Related

Query To Replace Part Of Field In PHPMyAdmin

Nov 27, 2011

I have a table called 'wp_postmeta' (Wordpress) which contains a column called 'meta_value'. A typical field in this column looks like this:

Code:
a:22:{s:12:"productimage";s:104:"http://www.disobeyclothing.com/wp-content/themes/eCommerce3/images/tshirts/SurveillanceSociety-Black.png";s:13:"productimage1";s:104:"http://www.disobeyclothing.com/wp-content/themes/eCommerce3/images/tshirts/SurveillanceSociety-White.png";s:13:"productimage2";s:0:"";s:13:"productimage3";s:0:"";s:13:"productimage4";s:0:"";
s:13:"productimage5";s:0:"";s:13:"productimage6";s:0:"";s:5:"pr

[code]...

I've tried to run the following query but each time I run it, it wipes all data in the field:

Code:
UPDATE wp_postmeta set meta_value=replace(meta_value, 'URL...')

I've also tried this query, but it has the same effect:

Code:
UPDATE wp_postmeta
SET meta_value = replace(LTRIM(RTRIM(meta_value)), URL...

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

Stored Procedure - Replace Null With Text Msg

Jan 27, 2006

Hi

I'm trying to create a stored procedure using the northwind db which will do the following:

SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock
FROM Products

However, where UnitsInStocks = 0 I would like the words "Sorry, out of stock" to appear. I will then call this from an ASP page.

Can anyone help please?

Cheers



Woolly

View 7 Replies View Related







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