Good day to all Guru.
I'd like to ask how to trim a white space from field string.
I have this example:
uniqueID: fieldName:
00001 PP. STRING
00002 PP.STRING
00003 PP. STRING
00004 PP. STRING

I want an SQL to update the fiendName with the new string into 'PP.STRING' by deleting the pointed white space.

Any help will be appreciated very much. Thanks in advance.

kriz


More Power!

I have managed to take out the '. ' and '.' strings by using instr() and substr() functions. Let's say your table name is DUMMY2, following is the query that can help you:


select substr(fieldName,1,instr(fieldName,'.',1)-1)||substr(fieldName,instr(fieldName,'.',1)+1,leng th(fieldName)-instr(fieldName,'.',1))
from dummy2
where instr((substr(fieldName,1,instr(fieldName,'.',1)-1)||substr(fieldName,instr(fieldName,'.',1)+1,leng th(fieldName)-instr(fieldName,'.',1)) ),' ',1)=0;

RESULT: PPSTRING

You can update the column by using the UPDATE statement.Reply me back if anymore help is needed.
Waiting for your reply if needed
Maxood

The following query does exactly what you are asking for:

update dummy2
set fieldName=(select substr(fieldName,1,instr(fieldName,'.',1)-1)||substr(fieldName,instr(fieldName,'.',1)+1,leng th(fieldName)-instr(fieldName,'.',1))
from dummy2
where instr((substr(fieldName,1,instr(fieldName,'.',1)-1)||substr(fieldName,instr(fieldName,'.',1)+1,leng th(fieldName)-instr(fieldName,'.',1)) ),' ',1)=0);


MAXOOD!

Life is an endless journey towards perfection

Hi there thanks for your immediate response. The script you gave me did not work in access. :( Sorry to say that. But im pleading, can you convert this query for MSAccess.



More Power!