Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 9th, 2007, 10:16 PM
Registered User
 
Join Date: Nov 2004
Location: manila, NCR, Philippines.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to vickr1z
Default TRIM WHITE SPACE

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!
  #2 (permalink)  
Old January 11th, 2007, 09:56 AM
Authorized User
 
Join Date: Jan 2007
Location: , , .
Posts: 46
Thanks: 2
Thanked 1 Time in 1 Post
Default

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

  #3 (permalink)  
Old January 11th, 2007, 11:17 AM
Authorized User
 
Join Date: Jan 2007
Location: , , .
Posts: 46
Thanks: 2
Thanked 1 Time in 1 Post
Default

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
  #4 (permalink)  
Old January 12th, 2007, 12:39 AM
Registered User
 
Join Date: Nov 2004
Location: manila, NCR, Philippines.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to vickr1z
Default

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!


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can i trim space using XSLT? Mitali XSLT 5 July 25th, 2008 03:15 PM
White space is required between the public identif ashok.andena XSLT 2 June 2nd, 2008 05:27 AM
clean white space and comments Kabe XSLT 0 June 15th, 2007 03:34 AM
White-space preserved David P. Manning XML 1 April 24th, 2007 07:50 AM
White space sundar_revathi XSLT 2 May 17th, 2004 05:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.