simple space issue

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.
LVL 10
jaggernatAsked:
Who is Participating?
 
Dave FordConnect With a Mentor Software Developer / Database AdministratorCommented:

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
 
Dave FordSoftware Developer / Database AdministratorCommented:
select A.Description
from   schema.Employee A
where  UPPER(A.Description) like 'WEEKEND%MONKEY'
;

HTH,
DaveSlash
0
 
jaggernatAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
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
 
jaggernatAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:

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

0
 
jaggernatAuthor Commented:
>>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
 
Dave FordSoftware Developer / Database AdministratorCommented:

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
 
Kent OlsenData Warehouse Architect / DBACommented:

> 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
 
iyerbhuvanacConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.