Parsing out ( and ) characters 2

[John1Chr]

I don’t think this will be too difficult. I want to take out the( and )from some of the numbers that are in a field. Most of the records do not have the( and ) in them. There is always four characters in front of the dash. Within the parentheses the record might have a letter in it. In addition, the number of characters in the field varies. Examples are below:

2604-(26X03) should be 2604-26X03
4610-(96719) should be 4610-96719
5618-(5025) should be 5618-5025
2103-(95108) should be 2103-95108
5622-12 should be 5622-12 (unchanged)
5628-3 should be 5628-3 (unchanged)
4402-881 should be 4402-881 (unchanged)

 

[Golom]

Replace(Replace(TheString,"(",""),")","")

 

[ThomasLafferty]

Hi BrGenCaj! Why not keep it simple and use

[b]First Time:[/b]
Edit>Replace
Find What [!]([/!]
Replace With
Look In: [!]YourField[/!]
Match: [!]Any Part of Field[/!]

Then run it again:

[b]Second Time:[/b]
Edit>Replace
Find What [!])[/!]
Replace With
Look In: [!]YourField[/!]
Match: [!]Any Part of Field[/!]

If it is really necessary, you could write a VB procedure for this and make it a permanenet feature in your db, but for now the thing to determine is whether or not you really need that functionality or if a simple built in tool will do the job.

Hope this helps.

Tom

Born once die twice; born twice die once.

 

[John1Chr]

Forgive me if I seem naïve, were you two thinking of having this set up as a function? Let’s say the field name is plop and it is in table name toilet. How would you write the total code? (Often, I feel like I am at an entry level with VB so if you feel like you are spoon feeding me you might be correct.)

 

[Golom]

I just gave you a function (Replace) that you can use in VBA or SQL.

In VBA

TheString = "2604-(26X03)"
TheString = Replace(Replace(TheString,"(",""),")","")
[COLOR=black cyan]' Produces 2604-26X03 in TheString[/color]

In SQL

UPDATE myTable 
SET TheField = Replace(Replace(TheField,"(",""),")","")
Where InStr(TheField,"(") > 0
   OR InStr(TheField,")") > 0

 

[John1Chr]

Thanks again, Golom.