simple space issue

select A.Description
from   schema.Employee A
where  UPPER(A.Description) like 'WEEKEND%MONKEY'
;

HTH,
DaveSlash

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

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')

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

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

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

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

> 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