Code Forums - View Single Post

technobard · 10-23-2007, 07:16 AM

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 '-'.

10-23-2007, 07:16 AM	#3 (permalink)
technobard Centurion Nova Prime Join Date: May 2002 Location: Oak Park, IL (USA) Posts: 285	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 '-'. __________________ It takes 2 points to draw a straight line, but at least 3 points to draw a conclusion.