replace content between Parentheses

[FLEQUE]

I have a recordset and some of the fields have some Parentheses with some information in between. I want to take out the Parentheses and the information in between. The information in between the Parentheses is not always the same.

[delaneypub]

You might be able to use a SQL functions like SUBSTRING, POSITION and/or CONCATENATE to remove the parenthetical text from the field value. Functions vary somewhat from vendor to vendor, so you should check the documentation for whatever DBMS you are using.

[FLEQUE]

I'm using asp/vbscript with an access database. Can you give me some code samples?

[delaneypub]

Youa re also using SQL if you are accessing a database. You can reformat the field value either in the SQL query, or use asp functions after the query returns the recordset.

The SQL functions (position, substring and concatenate) might look something like the following. (I'm not able to test this for you so I can't say that the code is error-free. Also, the syntax is for Postgres SQL, so you need to verify it agianst Access SQL):

substring(fieldname from 1 for position('(' in fieldname)) || substring(fieldname from ((position(')' in fieldname)+1)) for (octet_length - (position(')' in fieldname)))

where "fieldname" is the fieldname from the table you are querying.

If you are using DW to build your SQL query, you might start it on the Simple pane of that dialog, and then add the code above in the Advanced pane.