oracle query help please?

[bandababu]

table lo oka CLOB column data itla undi

COLUMN A
QUARTERLY BONUS IS NOT GOOD
5000
YEARLY CANDIDATE STATUS
WEAK

 

candidate status tharuvatha unna word 'weak' extract cheyyali   ? 

[vikuba]

NVL command 

or 

Locate Command

if string is of constant length then use SUBSTR

[SUbba LIngam]

select to_char(substr(columnA,instr(columnA,'WEAK'),4)) from table_name where instr(columnA,'WEAK') > 1

[bandababu]

2 minutes ago, SUbba LIngam said:

select to_char(substr(columnA,instr(columnA,'WEAK'),4)) from table_name where instr(columnA,'WEAK') > 1

status can change. WEAK or STRONG or MODERATE edaina undochu. appudu etla rayali query ?

[SUbba LIngam]

5 minutes ago, bandababu said:

status can change. WEAK or STRONG or MODERATE edaina undochu. appudu etla rayali query ?

will there be any specific position number with the status value in columnA?

[bandababu]

1 minute ago, SUbba LIngam said:

will there be any specific position number with the status value in columnA?

the value will be displayed below after status text 

[SUbba LIngam]

1 minute ago, bandababu said:

the value will be displayed below after status text 

status value tarvata inka emaina text unde chance unda?

[bandababu]

2 minutes ago, SUbba LIngam said:

status value tarvata inka emaina text unde chance unda?

yeah undi status value kinda 

employee type undi

[SUbba LIngam]

1 minute ago, bandababu said:

yeah undi status value kinda 

employee type undi

ok, try to find out the position numbers of both employee type and candidate status, the difference of those two values should be the status text right?

[bandababu]

3 minutes ago, SUbba LIngam said:

ok, try to find out the position numbers of both employee type and candidate status, the difference of those two values should be the status text right?

position varies man. CLOB column lo chala words untayi. query etla rayali ?

[SUbba LIngam]

1 minute ago, bandababu said:

position varies man. CLOB column lo chala words untayi. query etla rayali ?

select to_char(substr(columnA, instr(columnA,'CANDIDATE STATUS'), instr(columnA,'EMPLOYEE TYPE')-(instr(columnA,'CANDIDATE STATUS')+16)))  from table_name

P.S. i am not sure man without analyzing the data in clob column