Update part of delimited string in field. (1 Viewer)

[Sanguineus]

Hi All,

Really stuck on something that after a couple of daysis doing nothing more than giving me a headache.

I have been asked to look after/maintain as DB from a colleague who is moving on. I am going through updating code, forms and so on, but have come cross a problem I just can't crack.

For some reason I just can't fathom it was decided to take 3 inputs from a form and concatonate them into a string and then storethat in a table. (really annoying I know - changing this is on my to do list, but its a few months away as I also have to change some forms and some reporting outputs) for example: Input1-Input2-Input3

To give me time to make the adjustments I am building a few "admin" forms that will allow a couple of "select" colleagues the ability to make some "simple" adjustments.

What I am working on is a form that will allow the above users to "edit" the values in the controls (i.e. spelling and termiology corrections). I have sorted the part that goes through the selectable values and updates them, but now want to go through the historic records and make the changes there as well. If the recrds werestored correcltythis wouldn't be an issue; but due to the concacanation its rather difficult.

I have this udf:

Public Function SplitString(str As String, delimiter As String, count As Integer) As String
    Dim strArr() As String
    strArr = Split(str, delimiter, count + 1)
    count = count - 1 'zero-based
    If UBound(strArr) >= count Then
        SplitString = strArr(count)
    End If
End Function

which I can use in a select statement to successfully split the field

SELECT SplitString(FieldName,"-",1) AS SectionOne, 
SplitString(FieldName,"-",2) AS SectionTwo, 
SplitString(FieldName,"-",3) AS SectionThree
FROM TableOne

The bit I can't get my head around is how do I then update one of the sections in the main table. I'm sure the answer is 'releatively' simple but can't quite get there

 

[Uncle Gizmo]

I can't see how this Code relates to your problem? Please could you explain what it does....

SELECT SplitString(FieldName,"-",1) AS SectionOne, 
SplitString(FieldName,"-",2) AS SectionTwo, 
SplitString(FieldName,"-",3) AS SectionThree
FROM TableOne

 

[Sanguineus]

I can't see how this Code relates to your problem? Please could you explain what it does....

SELECT SplitString(FieldName,"-",1) AS SectionOne,
SplitString(FieldName,"-",2) AS SectionTwo,
SplitString(FieldName,"-",3) AS SectionThree
FROM TableOne

This code, takes a string from one field i.e. One-Two-Three and splits in into 3 fields One, Two and Three (using "-" as the delimeter)

EDIT::

So in my problem I might want to change 'Three' to '3' so Ihave records showing as One-Two-3 instead of One-Two-Three

 

[Micron]

Sounds like you're saying you have records in the usual sense, but for some reason, 3 (or some other number) of fields from each record were also stored in one field in some other table as concatenated or character (,) separated values. Did the primary key value from the 'normal' record get stored as a foreign key value in the concatenated record? If not, you might be looking at a strictly manual fix. I say that because if you alter "for" to "four" in the main records and try to run a matching update on the other table, you'll likely end up changing things you don't want - like "before" to "befour" everywhere. If the 2nd table had the pk as a foreign key value from table1 you would be much better off.

I have to wonder why, if you have the separated data, do you need the concatenated records at all?

 

[Sanguineus]

I don't have the seperated data pere that function would give me the seperated data, from the exsisting data. I am wondering if I have over complicated things. I would essentially know where in the string the substring I want to replace is so I could include the delimiter in the searchand replace withportions of the replace function.

i.e. One-Two-Three replace(FieldName,'-Three','-3')

in this instance I would know catergorically that 'Three' could only exsist in the 3rd slot... might have answered my own question here...

 

[arnelgp]

based on the Function you showed, you can Create another function
that will do the replacement:

sample:

dim x as string
x = "one-two-three"
'replace the second substring ("two") with 2
x= ReplaceSplitString(x, "-", 2, 2)
debug.print x

result: one-2-three

Public Function ReplaceSplitString(str As String, delimiter As String, count As Integer, strReplacement As String) As String
    Dim strArr() As String
    strArr = Split(str, delimiter, count + 1)
    count = count - 1 'zero-based
    If UBound(strArr) >= count Then
        strArr(count) = strReplacement
    End If
    ReplaceSplitString = Join(strArr, delimiter)
End Function

 

[Gasman]

I'd say, stop trying to be clever and trying to do it all in one go.?
Get your split data. Then run against that to decide what you wish to update. Then concatenate back if need be and it cannot be carried out on second query.?
If I understand correctly, this is not going to be a regular occurrence?

 

[arnelgp]

i thought you are wanting to Replace, position-wise on the string?

 

[Dreamweaver]

"edit" the values in the controls (i.e. spelling and termiology corrections)

Why not just use the spell cheker
Not sure whats going on with " Input1-Input2-Input3 " But it smells of a normalisation problem?

 

[Micron]

Based on post 5 I'd say just write an update query and use Replace just like you said. I don't see where all that complicated code is needed, and writing more of it just seems to add to that baggage.

Obviously I thought that you had 2 sets of records and would need to maintain a relationship, so I would agree that all that code for a simple update seems unwarranted. I would think that this would be good enough if it's OK to loop over all records and change all occurrences:

UPDATE table SET TableName.FieldName = Replace([FieldName],"OldText","NewText");

EDIT
I would try it on a table copy first. Keep in mind that the Replace function will act on partial matches as I've said. If you haven't presented real examples of your data, that makes it difficult if not impossible to get focused help. If whole word replacement is needed, you will likely need to use regular expression.

 

[Sanguineus]

Based on post 5 I'd say just write an update query and use Replace just like you said. I don't see where all that complicated code is needed, and writing more of it just seems to add to that baggage.

Obviously I thought that you had 2 sets of records and would need to maintain a relationship, so I would agree that all that code for a simple update seems unwarranted. I would think that this would be good enough if it's OK to loop over all records and change all occurrences:

UPDATE table SET TableName.FieldName = Replace([FieldName],"OldText","NewText");

I would try it on a table copy first.

Thank you this is pretty much what i've just finished doing, as you say was leading myself down an overly cmplicated solution when there was a perfectly good simple one staring me in the face. Taking the opportunity to talk it through helped (as it often does for me).

Just one tinyissue to think through now and that's the where clause so I don't update the wrong values.

i.e. just changing the "two" in the 1st row now the second, but Ithink Iknow what i need to do here (famous last words)

One-Two-Three
Two-Two-Three

Thank you all

 

[Isaac]

Based on post 5 I'd say just write an update query and use Replace just like you said. I don't see where all that complicated code is needed, and writing more of it just seems to add to that baggage.

Obviously I thought that you had 2 sets of records and would need to maintain a relationship, so I would agree that all that code for a simple update seems unwarranted. I would think that this would be good enough if it's OK to loop over all records and change all occurrences:

UPDATE table SET TableName.FieldName = Replace([FieldName],"OldText","NewText");

EDIT
I would try it on a table copy first. Keep in mind that the Replace function will act on partial matches as I've said. If you haven't presented real examples of your data, that makes it difficult if not impossible to get focused help.

+1 vote for Micron's method, and just to add you could add a wee bit of safety to your Replace() operation by replacing
-value-
with
-newvalue-

rather than replacing
value
with
newvalue

To ensure you don't replace fore in Before but only -fore- if it exists.

I'm sorry, but I can't avoid the humor present here; this post started out as correcting someone else's spelling errors and then was covered in typos and spelling errors almost hard to read.

 

[Micron]

I like the idea of including the separators - would avoid having to use regular expressions, methinks.