Search for specific characters in a text field (1 Viewer)

[Newman]

I have a table with a large text field in it, among other fields.
What I am trying to do, in a query, is to show only the characters that are between brackets "[" and "]" for that field. And, if there is more than one pair of brackets, show only what lies between the last pair of brackets.

What I've tried so far is use InStr() functions to find these brackets and then use a Mid() function to show the enclosed text. But, it doesn't work well and it gives me a very complex query! In fact, I don't think it is a good idea to even use these functions in my query. That would probably slow it down a lot.

Can someone show me a function that I could use to do what I'm looking for? I need function names that could help me make better searches for more informations.

Thank you!

 

[cheuschober]

I'm pretty sure InStr() is the function you want unless you want to start calling characters out by their numeric values.

Are you opposed to creating a user-defined function to do what you're asking?

Your query would then be no more complex than any regular query. Though performance would still be affected. If you have MASSIVE amounts of records you could run the function on a subset using the following criteria in the where statement of your text field:

Like "*[*]*"

In that case you at least know you're not unnecessarily scanning records that aren't in need of 'scrubbing'.

Hope this helps some. If I get to my db at work and someone far more qualified hasn't helped I'll see what I did a few months ago when I ran into a similar problem.

regards,
~Chad

 

[Newman]

Thanks Chad,
I forgot that we can use user-defined functions in a query.
Unless someone else gives a better hint, I'll go with it.
Thanks again!