PC Review


Reply
Thread Tools Rate Thread

search and replace query for a value in every column

 
 
mcnews
Guest
Posts: n/a
 
      31st Jul 2012
how can i search and replace a value in every or selected columns?

tia,
mcnewsxp
 
Reply With Quote
 
 
 
 
Gene Wirchenko
Guest
Posts: n/a
 
      31st Jul 2012
On Tue, 31 Jul 2012 10:31:40 -0700 (PDT), mcnews
<(E-Mail Removed)> wrote:

>how can i search and replace a value in every or selected columns?


update thetable
set selectedcolumn=newvalue
where selectedcolumn=searchvalue
and repeat as needed.

Sincerely,

Gene Wirchenko
 
Reply With Quote
 
 
 
 
John W. Vinson
Guest
Posts: n/a
 
      1st Aug 2012
On Tue, 31 Jul 2012 10:31:40 -0700 (PDT), mcnews <(E-Mail Removed)>
wrote:

>how can i search and replace a value in every or selected columns?
>
>tia,
>mcnewsxp


How depends on what. In a properly normalized table, replacing a value in
every column should be a vanishingly rare operation! Each column would be a
different "Attribute" of the entity represented by the table; it would make no
sense at all to update (say) the LastName, Birthdate, Height and EyeColor
fields replacing the same value in all.

What's the structure of your table, and what change are you proposing?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com
 
Reply With Quote
 
mcnews
Guest
Posts: n/a
 
      2nd Aug 2012
On Tuesday, July 31, 2012 9:21:34 PM UTC-4, John W. Vinson wrote:
> On Tue, 31 Jul 2012 10:31:40 -0700 (PDT), mcnews <(E-Mail Removed)>
>
> wrote:
>
>
>
> >how can i search and replace a value in every or selected columns?

>
> >

>
> >tia,

>
> >mcnewsxp

>
>
>
> How depends on what. In a properly normalized table, replacing a value in
>
> every column should be a vanishingly rare operation! Each column would bea
>
> different "Attribute" of the entity represented by the table; it would make no
>
> sense at all to update (say) the LastName, Birthdate, Height and EyeColor
>
> fields replacing the same value in all.
>
>
>
> What's the structure of your table, and what change are you proposing?
>
> --
>
>
>
> John W. Vinson [MVP]
>
> Microsoft's replacements for these newsgroups:
>
> http://social.msdn.microsoft.com/For...-US/accessdev/
>
> http://social.answers.microsoft.com/.../en-US/addbuz/
>
> and see also http://www.utteraccess.com


i receive data from different places in different formats that in theory issupposed to be the same data. i have no control how over column names orhow each place stores the same type of info. so much scrubbing is to be done. so after doing the analysis on each data source i can tell what data is junk and needs to vanish.
 
Reply With Quote
 
John W. Vinson
Guest
Posts: n/a
 
      2nd Aug 2012
On Thu, 2 Aug 2012 09:33:35 -0700 (PDT), mcnews <(E-Mail Removed)>
wrote:

>i receive data from different places in different formats that in theory is supposed to be the same data.

i have no control how over column names or how each place stores the same
type of info. so much scrubbing is to be done. so after doing the analysis
on each data source i can tell what data is junk and needs to vanish.


I guess I still don't understand the question.

It sounds like you have arbitrary tables, with arbitrary and unpredictable
fieldnames, with arbitrary and unpredictable contents - and you're asking for
some automated procedure to whip it into shape. I don't think the science of
artificial intelligence has progressed to that extent!

Could you please explain what it is you want us volunteers to suggest or
provide?

I can imagine a table with fields like Tablename, Columnname, NewColumnName,
and a form to manage it, but beyond that I'm just not sure what you're asking!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com
 
Reply With Quote
 
mcnews
Guest
Posts: n/a
 
      4th Aug 2012
On Thursday, August 2, 2012 1:16:13 PM UTC-4, John W. Vinson wrote:
> On Thu, 2 Aug 2012 09:33:35 -0700 (PDT), mcnews <(E-Mail Removed)>
>
> wrote:
>
>
>
> >i receive data from different places in different formats that in theory is supposed to be the same data.

>
> i have no control how over column names or how each place stores the same
>
> type of info. so much scrubbing is to be done. so after doing the analysis
>
> on each data source i can tell what data is junk and needs to vanish.
>
>
>
>
>
> I guess I still don't understand the question.
>
>
>
> It sounds like you have arbitrary tables, with arbitrary and unpredictable
>
> fieldnames, with arbitrary and unpredictable contents - and you're asking for
>
> some automated procedure to whip it into shape. I don't think the science of
>
> artificial intelligence has progressed to that extent!
>
>
>
> Could you please explain what it is you want us volunteers to suggest or
>
> provide?
>
>
>
> I can imagine a table with fields like Tablename, Columnname, NewColumnName,
>
> and a form to manage it, but beyond that I'm just not sure what you're asking!
>
> --
>
>
>
> John W. Vinson [MVP]
>
> Microsoft's replacements for these newsgroups:
>
> http://social.msdn.microsoft.com/For...-US/accessdev/
>
> http://social.answers.microsoft.com/.../en-US/addbuz/
>
> and see also http://www.utteraccess.com


i worked it out. i just loop through each row of the table and loop through the field names. i then do a string compare with a loaded string array and if i find what is in the array i execute an update statement on the entire column. it works fine.
thanks.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to search in one column, and replace in the other column if fo Adnan Microsoft Excel Misc 2 14th Nov 2008 02:03 PM
Search for value in Column A and return the value in Column B minismood Microsoft Excel Discussion 5 25th Jan 2006 06:07 PM
Search for a value in column A and return the vaule/values in column B to column C minismood Microsoft Excel Discussion 3 10th Jan 2006 11:02 AM
CHART: how to show (on every column top) the value of every column ? Martin Microsoft Access 0 23rd Nov 2005 08:38 AM
Replace methode, Replace Function, Stringbuilder replace, Regex Replace, Split Cor Microsoft VB .NET 4 1st Mar 2004 03:50 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:11 AM.