seperate one column to different (or) seperate if a column has both first_nm and last_nm using INSTR function

select column_nm,

substr( column_nm ,1,instr( column_nm , ',') - 1) First_nm,

trim(substr( column_nm ,instr ( column_nm, ',')+1)) last_nm

 from table_nm

Need count of a character that how many times it repeat

Oracle:
select regexp_count('SRILANKA','A',1,'c') cnt from dual.
Informatica:
SRC-->SQ-->EXP-->TGT

In expression create two variable ports
in_NAME
v_REPLACE_A_CNT=LENGTH(REPLACECHR(0,NAME,'A',''))
v_NAME_CNT=LENGTH(NAME)
out_CNT=v_NAME_CNT-v_REPLACE_A

Target:
connect out_CNT to target port

If you pass 'SRILANKA' as NAME then out put is 2.

if the value is in mb or CLOB data type

SUBSTR(REPLACE(REPLACE(REPLACE(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'to')+3),CHR(10),' '),CHR(13),' '),CHR(124),' '),1,40) AS DESCRIPTION
 or

Use DBMS_LOB.SUBSTR(Column_name,250,1)