CODEWITHSUNDEEP
oracle-database
totalitarian
totalitarian

Reputation: 3666

Return whole string if it cannot be split using substr / instr

My data looks like this

READINGS
----------
100 - Hot
200 - Cold
300 - Warm
80
8989970

I need to return just the first part of each line like so

READINGS
----------
100
200
300
80
8989970

I've tried the following

SUBSTR(READINGS,0,instr(READINGS,' - ')-1)

But if the instr() failes to find ' - ' the field ends up null like so

READINGS
----------
100 - Hot
200 - Cold
300 - Warm
null
null

Any advice on how to solve this?

Upvotes: 1

Views: 76

Answers (1)

Aramillo
Aramillo

Reputation: 3216

You can use a case. Something like:

CASE WHEN instr(READINGS,' - ') = 0 THEN READINGS 
ELSE SUBSTR(READINGS,0,instr(READINGS,' - ')-1) END

Upvotes: 1

Related Questions