Search space in string:

SELECT column_name FROM TABLE_NAME WHERE REGEXP_LIKE(column_name,' ');

 

SELECT column_name FROM TABLE_NAME WHERE column_name like '% %';

 

SELECT column_name FROM TABLE_NAME WHERE column_name like '%' || CHR(32) || '%';

Remove space from a string:

Examples:

Remove space from beginning and end of the string:

select trim('  Hello! I am Here ') from dual;

 

update table

set column_name=trim(column_name)

where REGEXP_LIKE(column_name,' ');

 

Remove space only from the beginning of the string:

select ltrim('  Hello! I am Here ') from dual;

 

update table

set column_name=ltrim(column_name)

where REGEXP_LIKE(column_name,' ');

Remove space only from the end of the string:

select rtrim('  Hello! I am Here ') from dual;

 

update table

set column_name=rtrim(column_name)

where REGEXP_LIKE(column_name,' ');

 

Remove space completely from string:

select replace('  Hello! I am Here ', chr(32), '') from dual;

update table

set column_name=replace(column_name, chr(32), '')

where REGEXP_LIKE(column_name,' ');