Solved

simple space issue

Posted on 2007-11-30
11
345 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
 
LVL 45

Assisted Solution

by:Kdo
Kdo 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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:Kdo
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Change the sort Order 5 255
DB2 process keep going heavy and memory usage high 13 1,266
DB2 V9.7 restore 5 28
Another SQL with parm Part 2 44 52
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now