X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Return first 3 characters - but not articles, Any Version    
post Dec 4 2017, 02:01 PM

Posts: 10
Joined: 13-January 09

NOTE: This question was originally posted as a reply to this discussion from 2010.

How would you do this ignoring articles?(ie. the, a, and, etc)
Go to the top of the page
John Vinson
post Dec 4 2017, 02:09 PM

UtterAccess VIP
Posts: 4,270
Joined: 6-January 07
From: Parma, Idaho, US

What's the context? Do you have records in your table where the trade is listed as (say) "a laborer"? Or (shudder) "an applaborer"?

And what are you going to do if you have to deal with an "Application developer" who isn't an apprentice?

You REALLY need to correct the structure of your table. You should have a table of Trades, with a numeric primary key (such as an autonumber); tables using trades should have just a numeric TradeID field linked to this table; and a separate field for statuses (apprentice, journeyman, master, trainer, ....). You're using a relational database - use it relationally!
Go to the top of the page
post Dec 4 2017, 02:48 PM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Depending on how long your list of articles is, you could try creating a nested IF with each unique article being removed from the original string with a Replace() function. A user-defined function might be better if you are talking about more than three or four articles, though. That way you can have as many as you like, and your query will be easier to read.

Either way, the more articles you are removing times the number of records in your query may equal a number high enough that the query will noticeably slow down.

Hope this helps,

Go to the top of the page
post Dec 4 2017, 09:03 PM

UA Admin
Posts: 33,767
Joined: 20-June 02
From: Newcastle, WA

Hm. As John and Dennis suggest, a task of this nature sometimes suggests a less than ideal table design is behind the problem. smirk.gif

While it is possible to write code that tries to eliminate certain bits and pieces of a field, it's probably more efficient to figure out how to avoid that in the first place.

In other words, it's fine to get into elegant parsing routines if you have no choice about dealing with dirty data, but sometimes it's just better to avoid letting the data get dirty to begin with.

To that end, please give us the context and provide some sample data to review.


Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th October 2018 - 01:48 AM