CODEWITHSUNDEEP
sqloracle-database
Rob Blagg
Rob Blagg

Reputation: 280

Inserting a value into a table if it matches the criteria otherwise insert null

I want to insert a value into a table using a value of SUBSTR(data, 71, 2).

However if data isn't long enough then I want to input null.

Upvotes: 0

Views: 47

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

You may use a CASE expression here:

CASE WHEN LENGTH(data) >= 73
     THEN SUBSTR(data, 71, 2)
     ELSE NULL END

This would be part of your larger insert statement, which you did not reveal to us.

Edit:

Calling SUBSTR on a column with a starting position which is out of bounds will already return NULL. However, if the starting position is within bounds and the length exceeds the length of the string, then only the available remainder of the string will be returned. Hence, the following call to SUBSTR will return world:

SUBSTR('hello world', 7, 30)

Upvotes: 3

Related Questions