Data Warehouse / Database Architect

Kent Olsen

Sam OZ

<div>
Try <pre><code class="code-10 nolinks" id="code-20-43312439-1">

declare
&nbsp; v_str varchar2(30):='00tag1';
&nbsp; v_str2 varchar2(30);
&nbsp; v_p0 number(12);
begin
&nbsp; v_p0:=length(v_str);
&nbsp; v_str2:=replace(substr(v_str,1,2),'00','0')||substr(v_str,3,v_p0-2);
&nbsp; dbms_output.put_line(v_str2);
end;
/



</code></pre> 
</div>

<div>
Don't attempt to use what Peter posted. &nbsp;It has unnecessary steps, uses PL/SQL code and only works for the single hard-coded value. 
 
If you have a LOT of data, what Ryan posted is likely the quickest. 
 
If you only have a few hundred/thousand rows, I might consider at regex: 

<pre><code class="code-1 nolinks" id="code-20-43313515-1"> regexp_replace(tagno,'^00','0')
</code></pre>
 
The regexp function calls are really expensive and you can notice a performance difference when dealing with really complex regex expressions and millions of rows. 
 
This regexp isn't complex and for a few thousand rows, I doubt you'll notice a performance difference.
</div>

<div>
<div class="content wysiwyg-content">
I have an oracle table TB-Tag with column tagno 
sample entreis 
00tag1 
0tag2 
1tag3 
3tag00 
I am looking an sql that should shold 
show tagno with leading 00 replaced as 0 
result should show as 
0tag1 
0tag2 
1tag3 
3tag00
</div>
</div>

I have an oracle table TB-Tag with column tagno
sample entreis
00tag1
0tag2
1tag3
3tag00
I am looking an sql that should shold 
show tagno with leading 00 replaced as 0
result should show as
0tag1
0tag2
1tag3
3tag00

oracle sql query to replace leading doubling 0

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

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.