Jump to content

A script to remove a single digit?


This topic is 2373 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

Once a year I must manually delete the number 6 from a field from anywhere between 300-600 records. I do this because I was informed that there is no way to automate the task since we also have other data in there, some of the data will contain a 6 (i.e. 16). The 6 can be by itself (no spaces), at the beginning of the string, the middle or the end. There are always spaces between the different numbers.

I usually do this task on New Year's day and I would LOVE to spend my time enjoying the first day of the year in a different way, but I have not been able to come up with a solution - after trawling google and many forums, I'm thinking wasting my time and should just suck it up this weekend, but before I give up, I wanted to check if anyone has any ideas.

TIA!

Edited by schrodinger
Link to comment
Share on other sites

39 minutes ago, schrodinger said:

The 6 can be by itself (no spaces), at the beginning of the string, the middle or the end. There are always spaces between the different numbers.

I am not sure I understand your description. If you mean the 6 you want to delete is always a word (where words are separated by spaces), then I believe LaRetta has you covered.

 

Anyway, I wanted to concentrate on another part:

42 minutes ago, schrodinger said:

Once a year I must manually delete the number 6 from a field from anywhere between 300-600 records.

There is something fundamentally wrong with your setup if you need to do this - whether manually or automated.

  • Like 1
Link to comment
Share on other sites

13 minutes ago, LaRetta said:

I deleted my suggestion because if the field only contained a single 6 or contained a 6 on second line of a multiline, it would not work.

I believe it would work in the former case, and I am not convinced you need to be concerned with the latter.

 

Edited by comment
  • Like 1
Link to comment
Share on other sites

Oh, right!  I entered it in a rush.  I was unsure also if the numbers were in a sentence, ending with a period.  And what about other non-space word break symbols.  I balked when I considered carriage returns as well.  It would sure make sense to break that '6' out of the string since it indicates it has different meaning.

  • Like 1
Link to comment
Share on other sites

  • Newbies

You could not be more right @comment!! The system is fundamentally flawed. But it's the DB that has not been built well for over 20 years, we have been assured that we will move to a new system over the last 3 years or more, I expect another couple of years, or more. But it is what it is....for this year....

This field is full of "disposition codes" for the year, the 6 code indicates it's still open, it gets moved to the newly created box for the year, but is among a number of other disposition codes. While I would love to change this system, I don't think it's in my hands...yet.

@LaRetta I would like to look at your solution with a fresh brain tomorrow.

I really appreciate your time looking at my bizarre question/situation.

6 minutes ago, LaRetta said:

Oh, right!  I entered it in a rush.  I was unsure also if the numbers were in a sentence, ending with a period.  And what about other non-space word break symbols.  I balked when I considered carriage returns as well.  It would sure make sense to break that '6' out of the string since it indicates it has different meaning.

 

28 minutes ago, comment said:

I believe it would work in the former case, and I am not convinced you need to be concerned with the latter.

 

Yes, no line breaks in this field. It's usually different "codes" - e.g. 10 7 16 6 - no periods, but often the 6 can be anywhere in there. Hope that makes some kind of sense

Link to comment
Share on other sites

  • Newbies
1 minute ago, comment said:

That is the key issue WRT to automating this task. We need to understand exactly how the field is structured, including all possible variations.

Exactly! Ok, it's an open text field and there are 15 numbers that can be in there (usually 1-5). The numbers are entered manually, so the 6 can be anywhere in the field (e.g. "1  6  10" or "6 7 2" or "12 10 7 6" etc).

If it seems like the whole concept was created by an insane person, you've nailed it. 

I hope this answers your questions, if I've missed the mark, I'm sorry, please let me know and thank you for your patience.

Link to comment
Share on other sites

Well, as I said earlier, if the values are separated by spaces, and you want remove a value consisting of a single digit 6, then LaRetta's solution should work for you. IIRC, it went like this:

Let ( 
pad = " " & YourField & " "
;
Trim ( Substitute ( pad ; " 6 " ; " " ) )
)

You can test this using "6 66 666 6 666 66 6" as the input. Note, however, that it will fail with "10 11 6 6 12 13": only one of the consecutive values will be removed.

  • Like 1
Link to comment
Share on other sites

You can also export the fields to a csv file, open it in word pad and do a find/replace taking care of clicking on "Match Whole Word Only".

Find 6

Replace with (leave empty)

This will remove all 6's but will leave alone numbers like 16 and 66.

 

I tried with the following:

1 12 6 2
1 2 6 6 16 9
1 15 29 66 12 6

And after doing as above got the following result

1 12  2
1 2   16 9
1 15 29 66 12 

Then you import the records again.

It is fully manual, but since it is only once a year it should be OK.

Best of luck

Carlos 

 

  • Like 1
Link to comment
Share on other sites

This topic is 2373 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.