Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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!
Reply With Quote
  #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

Reply With Quote
  #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
Reply With Quote
  #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!
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:50 PM.


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