4 Replies Latest reply on Mar 9, 2007 9:41 AM by NeptuneTG

    Updating part of a column

      I have a column in a SQL database which contains data (numbers separated by a pipe sign ex: 23|2|5) and I want to write something that will update the second number which in this case, 23|2|5, it would be 2. Can someone help me out on this?
        • 1. Re: Updating part of a column
          insuractive Level 3
          How about something like this:

          1) Retrieve records using query
          2) Either replace the middle value using list functions:
          #ListFirst(myColumn, "|")#|#newValue#|#ListLast(myColumn, "|")#

          or use regular expressions:
          #ReReplace(myColumn, "([0-9]*)[|]([0-9]*)[|]([0-9]*)", "/1|#myValue#|/3")#

          3) Insert value back into database

          Depending on your database, you may even be able to work your regular expression statement into your SQL.
          • 2. Re: Updating part of a column
            Dan Bracuk Level 5
            Normalize your database and then you can simply update the record you want.
            • 3. Re: Updating part of a column
              NeptuneTG Level 1
              I'm impressed. Thanks for the advice!
              • 4. Re: Updating part of a column
                NeptuneTG Level 1
                I finally acheived what I needed to but not by taking Dan's advice.

                Thanks Dan for your advice, but what is one to do if they are in a situation where they are working with a vendors' database and do not have the luxury to normalize their database? ( which is where I have found myself)

                Here is an example of the field i'm trying to update: LAYOUT=BOTTOM|70|30,TOP=,LEFT= 13|4|2|3|22,RIGHT=14|1|10]
                (My goal is to insert a |23 between the 13|4)

                <cfquery name="Rs" datasource="FusionDox">
                SELECT *
                FROM dbo.FDOX_USERINFO

                <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
                <html xmlns=" http://www.w3.org/1999/xhtml">

                <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
                <title>Plug-Ins Update</title>
                <style type="text/css">
                .style1 {color: #FF0000}

                <cfoutput query="Rs">
                <!--- finds the postion in the string--->
                <cfset variables.pos = FIND("LEFT=13|", rs.PluginSelections, 1)> <!---the 1 is the starting position--->

                <!---once you found it load left side into myvar1 and will count 7 characters past the first position--->
                <cfset variables.myvar1 =#MID(rs.PluginSelections, 1, variables.pos + 7)#>

                <!---once you found it load right side into myvar1 and will count 7 characters past the first position--->
                <cfset variables.myvar2 = #MID(rs.pluginselections, variables.pos + 8, LEN(rs.pluginselections) - variables.pos)#>

                <!---once you have the left and the right counted apart then you just plug in the myvar3 variable--->

                <cfset variables.myvar3 = variables.myvar1 & "23|" & variables.myvar2 >

                #rs.pluginselections#  |   #variables.pos# <br />
                1: #variables.myvar1# <br />
                2: #variables.myvar2# <br />
                <b>3: #variables.myvar3#</b>
                <br />

                <cfset variables.myvar4 = Replace(rs.pluginselections, "|||", "|", "ALL")>

                <span class="style1"><strong>4: #myvar4#<br />
                <br />
                <!---<cfquery name="Rs2" datasource="FusionDox">
                UPDATE FDOX_UserInfo
                set FDOX_UserInfo.PluginSelections = '#myvar3#'
                WHERE FDOX_UserInfo.userid = #rs.userid#