By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

a2k - how to split up field contents

P: n/a
Hi,

It's been a while since I posted but good ol' google groups is
confusing me these days and not giving me nice answers.

I have this field in a table with some data

HI1
HI2
HI1
ENG1
GEO1
ENG2
GEO2
GEO3
HI3
HI3
GEO3
ENG23

They will always be alphanumeric and end in a number.
I'd like to be able to query this and end up with the following values;

HI
ENG
GEO

So what happens is that we lose the numbers and then find the unique
remaining occurrences.

Can a SELECT query handle this?
Merry Xmas!
Martin
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
aircode

select left(fld,len(fld)-1) from myTable group by left(fld,len(fld)-1)


On 29 Dec 2004 12:14:57 GMT, "Pilocarpine" <ho**@furball.com> wrote:
Hi,

It's been a while since I posted but good ol' google groups is
confusing me these days and not giving me nice answers.

I have this field in a table with some data

HI1
HI2
HI1
ENG1
GEO1
ENG2
GEO2
GEO3
HI3
HI3
GEO3
ENG23

They will always be alphanumeric and end in a number.
I'd like to be able to query this and end up with the following values;

HI
ENG
GEO

So what happens is that we lose the numbers and then find the unique
remaining occurrences.

Can a SELECT query handle this?
Merry Xmas!
Martin

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Nov 13 '05 #2

P: n/a
Jack MacDonald wrote:
aircode

select left(fld,len(fld)-1) from myTable group by left(fld,len(fld)-1)


Is the 1 there to take off the final numeric? Only problem is that I
could have something like ENG123.

Nov 13 '05 #3

P: n/a
Yes it is. I missed your final example -- I thought they were all
single-digit suffixes.

You will need a custom VBA function to locate the breakpoint.

SELECT AlphaPart(fld) from myTable group by AlphaPart(fld)
(aircode - no error checking)
function AlphaPart(psFieldValue as string) as string
dim i as integer
for i = 1 to len(psfieldvalue)
if isnumeric(mid(psfieldvalue,i,1)) then exit for
next i
alphaPart = left(psfieldvalue,i-1)
end function

On 29 Dec 2004 20:57:36 GMT, "Deano" <ma**********@gmail.com> wrote:
Jack MacDonald wrote:
aircode

select left(fld,len(fld)-1) from myTable group by left(fld,len(fld)-1)


Is the 1 there to take off the final numeric? Only problem is that I
could have something like ENG123.

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Nov 13 '05 #4

P: n/a
Jack MacDonald wrote:
Yes it is. I missed your final example -- I thought they were all
single-digit suffixes.

You will need a custom VBA function to locate the breakpoint.

SELECT AlphaPart(fld) from myTable group by AlphaPart(fld)
(aircode - no error checking)
function AlphaPart(psFieldValue as string) as string
dim i as integer
for i = 1 to len(psfieldvalue)
if isnumeric(mid(psfieldvalue,i,1)) then exit for
next i
alphaPart = left(psfieldvalue,i-1)
end function

Cheers Jack, thanks for your time. I'll take a look at this once the
coffee works it's magic.
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.