<div>
The above accepted answer will fail if the data has a number after the # like '9201#CC1CC'<br />
<br />
the REPLACE probably isn't the best approach here since it looks at the entire string replacing things.<br />
<br />
Also depending on the amount of data you have, the regexp commands are an expensive function call to make.<br />
<br />
Here is an updated regexp that will work for everything before the #<br />
regexp_substr(mycol, '[^#]*')<br />
<br />
One that returns everthing fromthestart of the column until a non-number:<br />
regexp_substr(mycol, '^[0-9]*')<br />
<br />
The substr/instr that will likely be faster than the regexp calls:<br />
substr(mycol, 1, instr(mycol,'#')-1)
</div>


Database Developer & Administrator

Sean Stuber

EE Topic Advisor

PortletPaul

"Batchelor", Developer and EE Topic Advisor

Qlemo

<div>
<div class="content wysiwyg-content">
I’ve data in a table column that looks like this: <br />
9201#CCCC<br />
18183#DDD<br />
<br />
I’m looking for output as:<br />
<br />
9201 <br />
18183<br />
I’ve tried substr and instr and I’m getting desired result. Can someone please help? Thanks
</div>
</div>


I’ve data in a table column that looks like this: 
9201#CCCC
18183#DDD

I’m looking for output as:

9201 
18183
I’ve tried substr and instr and I’m getting desired result. Can someone please help? Thanks

data extract question.

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.