CODEWITHSUNDEEP
oracle-databasesql-loader
userprofile1
userprofile1

Reputation: 9

oracle ctl - add to POSITION a constant

I don't think if it is possible, but I lose some time in googling but with no results!

I need something like this example:

DATA:
(...)195231021(...)

CTL:
TEST        POSITION(37:40) '1'

Result:
0211   (last 3 numbers and append a constant number '1')

Help me please.

Upvotes: 1

Views: 889

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

You can apply an SQL operator to a field, so this works:

test position(37:39) "concat(:test, '1')"

You said you only wanted three characters so I changed the range from 37:40 to 37:39, but not sure if you actually want 38:40. (Of course, using the || concatenation operator works too, as EatÅPeach's answer shows).

Upvotes: 2

Noel
Noel

Reputation: 10525

From Ask Tom, try using bind variables.

TEST        POSITION(37:40) ":TEST || '1'"

Upvotes: 2

Related Questions