Replacing a string.


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Replacing a string.

Hybrid View

  1. #1
    Dennis Guest

    Replacing a string.


    I have a field in a sql7 db. The data in the field may look like this:
    1007;1000;10013

    I need to remove some data from the field but not all of it. I need to remove
    1000; and replace it with null. Any idea?

  2. #2
    Bernie Guest

    Re: Replacing a string.


    "Dennis" <dtucker01@hotmail.com> wrote:
    >
    >I have a field in a sql7 db. The data in the field may look like this:
    >1007;1000;10013
    >
    >I need to remove some data from the field but not all of it. I need to remove
    >1000; and replace it with null. Any idea?


    Hi,

    Update TableName Set FieldName = REPLACE(FieldName, '1000;', '')
    Where FieldName Like '%1000;%'

    All occurences of '1000;' will be replace with a nullstring which means removed
    from the string.

    Look up the REPLACE-function in BOL.

    Bernie

  3. #3
    Q*bert Guest

    Re: Replacing a string.


    Write a quick little VB app that selects the PK from the table and the field
    your needing to modify where the field needing to modify contains the 1000;
    Using the VB app, go through each record do a replace(fxn) on the record
    field and then peform the update.

    so something like...

    dim con as connection
    dim rs as recordset
    DIm SQL as string
    set con = getgonnection() 'Get connection is fxn that establishes connection
    to db
    SQL = "SELECT PK, Field from tblName where Instr(Field,'1000;')>0"
    rs.open (SQL,CON)
    While not rs.eof
    SQL = "Update tblName set Field = '" & Replace(rs.fields("Field"),"1000;","NULL;")
    & "' WHERE PK=" & rs.fields("PK")
    conn.execute(SQL)
    wend

    "Dennis" <dtucker01@hotmail.com> wrote:
    >
    >I have a field in a sql7 db. The data in the field may look like this:
    >1007;1000;10013
    >
    >I need to remove some data from the field but not all of it. I need to remove
    >1000; and replace it with null. Any idea?



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center