how to trim text in a column using sql query of ms access

[learning_graccess]

I have a column in a table (just for e.g.) like "3kg pen blue", "2kg pen white", "3 pencil red"

Now I want to trim this into only the item name like "pen", "pencil". I don't want 3kg or blue. How it can be done in sql query in ms access?

[hertfordkc]

string functions. If you are wanting the extract the middle group of characters from a string containing 3 groups of characters separated by blanks, you probably need to use INSTR and MID and LEN. Google 'Access string functions'.
I find working with query design much easier than trying to write SQL, so I would create a calculated field to extract the result, then go to SQL view to copy the SQL to where its needed.

[learning_graccess]

Thanks mate for your suggestion
mid function looks good for me but the problem is all name does not start with the same length.
for e.g.
"3pc pen blue" "3pieces pen green". Here as you can see if I put mid(columname,5,3)..it will not work for the second item...please help..

[hertfordkc]

MyVal = Right(LngStrng,Len(LngStrng)-Instr(1,Lngstrng," ")
That should return everything after the first blank.
MyVal = Left(MyVal,Instr(1,MyVal," ")
Should remove the right blank and following characters.

However, since you are wanting to do this in (a calculated field in) a query, doing the above would require two queries where only one is needed. So:

MyVal:Mid(LngStrng,Instr(LngString," ",1)+1,Len(LngStrng)-Instr(LngStrng," ",Instr(LngStrng," ",1)+1))

That is just my imagination. so be prepared to check it in pieces. I'll get on my Access PC if it doesn't work for you.
Note that the above assumes that there are not any leading or trailing blanks and that the field is not empty (or null?), If that were the case, you would need to surround it all with an iif statement to cut off errors.

[learning_graccess]

Thanks mate. It's working.