We can use the TRIM function to remove leading, trailing or both(leading and trailing)
spaces/characters from a string.

For example

SELECT trim('   Apple   ') FROM dual will return 'Apple'
SELECT trim(' '  from  '   Apple   ') FROM dual would return 'Apple'
SELECT trim(leading '5' from '555ABC') FROM dual would return 'ABC'
SELECT trim(trailing 'R' from 'AMPLERRRR') FROM dual  would return 'AMPLE'
SELECT trim(both '9' from '999NEW99')  FROM dual will RETURN 'NEW'
SELECT trim(both 'T' from 'TIIMT')  FROM dual will RETURN 'IIM'

LTRIM can be used to remove leading spaces

SELECT ltrim('   Apple   ') FROM dual will return 'Apple   '

RTRIM can be used to remove trailing spaces

SELECT rtrim('   Apple   ') FROM dual will return '   Apple';
SELECT LTRIM ('xyyxyxyxxyyyyyyyaxxyNEW WORD','xy')  FROM DUAL;
axxyNEW WORD


————————————————————————————————————————–
But what if the column contains some special characters which cannot be removed using TRIM?
It is sometimes useful to find out the ascii value of the character that you are trying to remove/replace. In the tables that are extracted from websites, some entries will have an &nbsp character which will not be removed by the trim command.

In such cases,use the ASCII function to find out the ascii value of the character.
Considering the invisible special character(like TAB or &nbsp) is in the first position then :

select ASCII(SUBSTR(column_name, 1, 1)) FROM table

A value of 160 will be returned for &nbsp.

Next, the REPLACE function can be used to remove it

UPDATE table
SET column_name=replace(column_name, CHR(160), '')

Example:

Create a table temp

CREATE TABLE temp_test(num VARCHAR2(6)) 

and insert the following records into it
with the leading spaces.

SELECT * FROM temp_test for update 

  2000
  1988
  2007
  1986
  1975
  1978
  1986
  1981

UPDATE temp_test SET num=TRIM(num)

will not remove the leading spaces.

SELECT DISTINCT ascii(substr(num,1,2)) FROM temp_test

will return

ASCII(SUBSTR(NUM,1,2))
---------------------------------------
160

From this, we know that the leading spaces are &nbsp.

Now use the REPLACE function with the CHR function. for more info on CHR visit http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions019.htm

UPDATE temp_test SET num=REPLACE(num,CHR(160),'')

Next

SELECT * FROM temp_test

and we can see that the leading characters are gone.

NUM
-----------------
200
198
2007
1986
1975
1978
1986
1981

Related posts:

  1. random in ORACLE

Leave a Reply

*