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