pl/sql truncating string after character

[aliaga61]

i have a value from a database field which is formatted as 123-45. I want to take 123 and assign it to variable a, and 45 and assign it to variable b, but I can't find what I need to do this. can i use the ltrim and rtrim functions? the problem is that the values left and right of the '-' can vary in length.

[redhead]

You need to use a split function, with '-' as the defined delimiter.

[technobard]

Another solution, using built-in Oracle functions, is to use the INSTR function to find the position of the '-' and then use the SUBSTR function to return portions of the string relative to the '-' position. For example:

select substr('123-45',1,instr('123-45','-',1,1)-1) from dual

will return: 123

instr('123-45','-',1,1) finds the first occurrence of '-' in the string starting with position 1. In the example above, I substract 1 from that position and use that as the end location of the string to the left of the '-'.

[aliaga61]

thank you both. the instr option fit my situation a little better.