aah, now I see the light again :-)
Thanks
Frank
-----Original Message-----
From: Richard Douglas [mailto:[Email Address Removed] Wednesday, April 23, 2003 10:56 AM
To: 'Kalis, Frank'; 'LazyDBA.com Discussion'
Subject: RE: Beginner Question
An update will automatically update all records, unless you put a where
condition in which limits it, in the case below it will only run on records
were the phone contains a '|' character.....
UPDATE sometable
SET phonestring = Replace(phonestring,phonestring,left(phonestring,
charindex(' ',phonestring)))
(where phonestring like '%|%')
-----Original Message-----
From: Kalis, Frank [mailto:Frank.[Email Address Removed]
Sent: 23 April 2003 09:44
To: LazyDBA.com Discussion
Subject: RE: Beginner Question
Sorry Richard,
brain dead wednesday, but how do you loop through the whole table with a
single update statement? Even if you are only changing one field?
Frank
> -----Original Message-----
> From: Richard Douglas [ mailto:[Email Address Removed] ]
> Sent: Wednesday, April 23, 2003 10:34 AM
> To: LazyDBA.com Discussion
> Subject: RE: Beginner Question
>
>
> You really should use an update statement as you are only changing one
> field there is no need for a cursor in this instance!
> -----Original Message-----
> From: Kalis, Frank [ mailto:Frank.[Email Address Removed] ]
> Sent: 23 April 2003 09:25
> To: LazyDBA.com Discussion
> Subject: RE: Beginner Question
>
>
>
> Hi,
>
> what has not been adressed yet, is that all these functions
> only change
> one record at a time. If you want to change all records in
> the table you
> must this within a stored procedure using a cursor or a while
> statement
> and update the original table with this new value. For this will only
> happen once, I would go for a cursor.
>
> HTH,
>
> Frank
>
>
> > -----Original Message-----
> > From: Richard Douglas [ mailto:[Email Address Removed] ]
> > Sent: Wednesday, April 23, 2003 10:13 AM
> > To: LazyDBA.com Discussion
> > Subject: RE: Beginner Question
> >
> >
> > Or you might want to try something like the following:
> >
> > Replace(phonestring,phonestring,left(phonestring, charindex('
> > ',phonestring)))
> >
> > This will replace the entire string with everything to the
> left of the
>
> > first space, of course this does assume that you have no
> spaces inside
>
> > your phonestring !
> >
> >
> >
> > -----Original Message-----
> > From: Kannan M [ mailto:[Email Address Removed] ]
> > Sent: 23 April 2003 09:02
> > To: LazyDBA.com Discussion
> > Subject: Re: Beginner Question
> >
> >
> > Hi Jaimie,
> >
> > If " |44|-2" is constant string, then u could use
> "Replace" command
> > with replace string as ''.
> >
> > Try this.......
> >
> > Kannan M
> >
> > ----- Original Message -----
> > From: "Jaimie Freitas" <jaimie.[Email Address Removed]
> > To: "LazyDBA.com Discussion" <[Email Address Removed]
> > Sent: Wednesday, April 23, 2003 1:01 PM
> > Subject: Beginner Question
> >
> >
> > > Hi All
> > >
> > > I need to remove all the |44|-2 from the end of all the
> > phone numbers
> > > in
> > my
> > > database. Any idea how I can do this?
> > > 02077940500 |44|-2
> > >
> > > Thanks for any help in advance
> > >
> > > warmest regards
> > >
> > >
> > > Jaimie Freitas
> > > The contents of this e-mail are strictly confidential and
> > are intended
> > only
> > > for the addressee named above. This e-mail may contain
> > confidential or
> >
> > > privileged information. If you are not the intended
> > recipient of this
> > > message please notify the sender immediately. The
> contents of this
> > > e-mail must not be disclosed to any other person or copies taken.
> > > Please note
> > that
> > > any views expressed in this e-mail may be those of the
> > originator and
> > > do
> > not
> > > necessarily reflect those of this organisation. Internet
> > e-mail is not
> >
> > > a secure communications medium. Please note this lack of
> > security when
> >
> > > responding by e-mail. Accordingly we give no warranties or
> > assurances
> > about
> > > the security and content of this e-mail and its
> attachments. Neither
>
> > Thermo
> > > Electron Corporation nor the sender accepts any responsibility or
> > liability
> > > for viruses and it is your responsibility to scan the e-mail and
> > > attachments. Any liability arising from any third party
> > acting on any
> > > information contained in this e-mail is hereby excluded.
> > (c)
> > Thermo
> > > Electron Corporation www.thermo.com
> > >
> > >
> >
> ---------------------------------------------------------------------
> > > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> > > unsubscribe, e-mail: mssqldba-[Email Address Removed] For
> > additional
> > > commands, e-mail: mssqldba-[Email Address Removed]
> > >
> > >
> >
> >
> ---------------------------------------------------------------------
> > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> > unsubscribe, e-mail: mssqldba-[Email Address Removed] For
> additional
> > commands, e-mail: mssqldba-[Email Address Removed]
> >
> >
> >
> >
> ---------------------------------------------------------------------
> > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > To unsubscribe, e-mail: mssqldba-[Email Address Removed]
> > For additional commands, e-mail: mssqldba-[Email Address Removed]
> >
>
>
MS Sql Server LazyDBA home page