Friday 2 February 2007

Search anywhere in a string with INSTR function

Type your summary here


Suppose you have a table X with the following data and you want to search for a particular string in its columns.


SQL>SELECT * FROM X;registration_origin

-------------------
aaa.bbbbb
ccccccc.dd
f.eeeeeeee

You can change this data using

SQL> SELECT REPLACE(SUBSTR(REGISTRATION_ORIGIN, 0, INSTR(REGISTRATION_ORIGIN, '.')), '.', ' ') FROM X
/

to this

registration_origin
-------------------
aaa
ccccccc
f