Updating part of a column

Report · Mar 08, 2007

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?

Report · Mar 08, 2007

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.

Report · Mar 08, 2007

Normalize your database and then you can simply update the record you want.

Report · Mar 08, 2007

I'm impressed. Thanks for the advice!

Report · Mar 09, 2007

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)

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

<!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">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Plug-Ins Update</title>
<style type="text/css">

</style>
</head>

<body>
<cfoutput query="Rs">

<cfset variables.pos = FIND("LEFT=13|", rs.PluginSelections, 1)> 


<cfset variables.myvar1 =#MID(rs.PluginSelections, 1, variables.pos + 7)#>


<cfset variables.myvar2 = #MID(rs.pluginselections, variables.pos + 8, LEN(rs.pluginselections) - variables.pos)#>



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

#rs.pluginselections# | #variables.pos# 
1: #variables.myvar1# 
2: #variables.myvar2# 
3: #variables.myvar3#
 

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

4: #myvar4# 

 



</cfoutput>

</body>
</html>

Adobe Community

Updating part of a column

1 Correct answer