to_number to handle character data

When you have column that could have data the to_number( ) function fails.

To correctly convert a field you can use the below sql:

 COALESCE(TO_NUMBER(REGEXP_SUBSTR( column_name, '^\d+')), 0)  column_name,
or

 COALESCE(TO_NUMBER(REGEXP_SUBSTR( column_name, '\d+')), 0)  column_name,

In SQL Server:

eg: SRCTAID='000000128'  get converted to '128'

SRCTAID='1-7383' does not get converted 

select SUBSTRING(a.SRCTAID, PATINDEX('%[^0]%',a.SRCTAID), LEN(a.SRCTAID)) as srctaidnew  a.srctaid
from  TABLE_NAME A ;