Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

simple space issue

Posted on 2007-11-30
11
Medium Priority
?
365 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:Dave Ford
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:
Dave Ford earned 600 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 300 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 46

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 46

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:Dave Ford
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 46

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 100 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

916 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