Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

simple space issue

Posted on 2007-11-30
11
358 Views
Last Modified: 2012-08-13
hi guys

I have a very small 'space' issue

this is my query

select A.Description from schema.Employee A where  RTRIM(UPPER(A.Description)) =
'WEEKEND MONKEY'

I have table Employee and the Description is
WEEKEND  MONKEY (but there is a extra space between two words and the query doesnt return the record)

 Can i modify my query such that i am able to return the record with extra space.
0
Comment
Question by:jaggernat
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 20384207
select A.Description
from   schema.Employee A
where  UPPER(A.Description) like 'WEEKEND%MONKEY'
;

HTH,
DaveSlash
0
 
LVL 10

Author Comment

by:jaggernat
ID: 20384262
ok. But..
when user enters ''WEEKEND MONKEY' as the description, he/she should get back the record with extra space in it ( 'WEEKEND   MONKEY') , in other words spaces between words should be ignored

>>>where  UPPER(A.Description) like 'WEEKEND%MONKEY'
its a search application where user would enter the description in the website screen and i am writing query in the back end, so my concern is how do i modify the query.

thanks
0
 
LVL 18

Accepted Solution

by:
daveslash earned 150 total points
ID: 20384368

In that case, I'd probably write a user-defined function to return the string with the extra spaces removed.

I don't have time to write the entire function for you, but I'll copy in another function I wrote a long time ago that you can model yours after.  (This one makes the first character of each word be uppercase.)

Obviously, you'll have to modify it for your own purposes, but It's a good start for you.

Once you have that written, your query becomes something like:

select A.Description
from   schema.Employee A
where  RemoveExtraSpaces(UPPER(A.Description)) = 'WEEKEND MONKEY'
;

HTH,
DaveSlash

CREATE FUNCTION ProperCaseByWord (      
    inString VARCHAR(100)               
)                                       
  RETURNS VARCHAR(100)                  
  LANGUAGE SQL                          
  modifies sql data                     
                                        
BEGIN                                   
-- Scratch variables used for processing
DECLARE outputString VARCHAR(100);      
DECLARE stringLength INT;               
DECLARE loopCounter INT;                
DECLARE charAtPos VARCHAR(1);           
DECLARE wordStart INT;                  
                                                                
-- If the incoming string is NULL, return an error              
IF (inString IS NULL) THEN                                      
 RETURN NULL;                                                   
End If;                                                         
                                                                
 -- Initialize the scratch variables                            
 SET outputString = '';                                         
 SET stringLength = LENGTH(inString);                           
 SET loopCounter = 1;                                           
 SET wordStart = 1;                                             
                                                                
 -- Loop over the string                                        
 WHILE (loopCounter <= stringLength) DO                         
    -- Get the single character off the string                  
    SET charAtPos = LOWER(SUBSTRING (inString, loopCounter, 1));
   -- If we are the start of a word, uppercase the character
   -- and reset the word indicator                          
   IF ((wordStart = 1) and (CharAtPos <> ' ')) then         
      SET charAtPos = UPPER (charAtPos);                    
      SET wordStart = 0;                                    
   END IF;                                                  
                                                            
   -- If we encounter a white space, indicate that we       
   -- are about to start a word                             
   IF (charAtPos = ' ') THEN                                
      SET wordStart = 1;                                    
   END IF;                                                  
                                                            
   -- Form the output string                                
   SET outputString = outputString || charAtPos;            
   SET loopCounter = loopCounter + 1;                       
END WHILE;                                                  
 
-- Return the final output
RETURN (outputString);    
END

Open in new window

0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 75 total points
ID: 20384469
Hi jaggernat,

It's probably simpler to ignore ALL spaces.  This has the odd side effect of accepting 'WEEK END MONKEY', 'WEEKEND    MONKEY', 'WEEKEN    DMONKEY', etc. but it's a very simple starting point.

SELECT A.Description
FROM schema.Employee A
WHERE replace (A.Description, ' ', '') = 'WEEKENDMONKEY';

[acknowledgement]

Crediting Dave for reminding me in an an earlier thread

[/ack]



Good Luck,
Kent
0
 
LVL 10

Author Comment

by:jaggernat
ID: 20384675
thanks dave and kdo
so kdo, are you saying that

SELECT A.Description
FROM schema.Employee A
WHERE replace (A.Description, ' ', '') = 'WEEKEND MONKEY';
 

will return the record with extra space in it? ('WEEKEND   MONKEY')
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 20384743
Hi jaggernat,

Nope.  It squeezes out all spaces so you need to compare the 'WEEKENDMONKEY'.

You can also use recursive SQL to normalize the data (compress all consecutive spaces into a single space) but that's probably more complicated that you want to get.



Good Luck,
Kent
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 20384752

What about cleaning up the data so that the extra space is removed?

0
 
LVL 10

Author Comment

by:jaggernat
ID: 20386484
>>What about cleaning up the data so that the extra space is removed?

how would i clean up the data? you mean the data in the tables?
0
 
LVL 18

Expert Comment

by:daveslash
ID: 20386516

Yes.  Having good data in the database is the best way to avoid troubles like this.

You'd probably have to write a stored procedure to run through it and remove all extra spaces, but the advantages of "clean" data cannot be stressed enough.

Regards,
DaveSlash
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 20395109

> You'd probably have to write a stored procedure

In a production shop, I'd whole-heartedly agree.

If you're running against a small dataset (or test data), run something like this:

  SELECT count(*) FROM employee WHERE description like '%  %';  -- note two spaces between the '%' characters

If the count is non zero, edit them:

  UPDATE employee SET Description = replace (description, '  ', ' ') WHERE description like '%  %';


Repeat these two statements until the returned count is 0.

On a large dataset, this will take a while.  The "like '%  %'" filter will require a full index or full table scan.  Fine on a few hundred thousand rows, but not a good idea on 10s of millions of rows.


Kent
0
 
LVL 1

Assisted Solution

by:iyerbhuvanac
iyerbhuvanac earned 25 total points
ID: 20721978
Try this

SELECT Description
FROM Emplyee
WHERE  REPLACE(A.Description, ' ', '') = REPLACE('WEEKEND MONKEY',' ','');

This way you can get still retain the input as 'WEEKEND MONKEY' (with just one space in between) and still get the record with 2 spaces( 'WEEKEND  MONKEY')
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question