oracle过滤分割字符串自定义函数

      该函数实现过滤前后的指定的字符串，诸如过滤分隔符等。可用于过滤字符串中的逗号分割符。特别说明：substr()函数支持从字符串倒数开始读取,例如：

 

dbms_output.put_line( substr('Hello World',-3,3)); 
执行结果：rld

 

 

    --过滤字符串前后的多疑字符，诸如过滤字符串前后的多余逗号
	Function  Fn_DislodgeSplitChar(
		P_FacultyList In Varchar2 Default '',
		P_FacultyChar In Varchar2 Default '',
		P_FacultyType In VARCHAR2 Default ''
	)   
	Return Varchar2
    As    
		v_FacultyList             Varchar2(32767) Default '';
		v_FacultyChar             Varchar2(8000)  Default '';
		v_FacultyType             Varchar2(20)    Default '';
		v_FacultyListLength       Pls_Integer    Default 0;
		v_FacultyCharLength       Pls_Integer    Default 0;
    Begin   
        v_FacultyList             := Trim(P_FacultyList);
		v_FacultyChar             := Trim(P_FacultyChar); 
		v_FacultyType             := Trim(P_FacultyType);
		v_FacultyListLength       := Length(v_FacultyList);
		v_FacultyCharLength       := Length(v_FacultyChar);
		
        If v_FacultyType = 'all' Then  
            v_FacultyType := 'leftright';
		End If;
		
        IF v_FacultyListLength > 0 Then 
	        If instr(v_FacultyType,'left') > 0 And Substr(v_FacultyList,1,v_FacultyCharLength) = v_FacultyChar Then 
			  v_FacultyList := Substr(v_FacultyList, -1, v_FacultyListLength - v_FacultyCharLength);
			End If;
						
			If instr(v_FacultyType,'right') > 0 And Substr(v_FacultyList,-v_FacultyCharLength, v_FacultyCharLength) = v_FacultyChar Then
			    v_FacultyList := Substr(v_FacultyList,1,v_FacultyListLength - v_FacultyCharLength);
		    End If;
		End If;
		
		Return v_FacultyList;       
	End Fn_DislodgeSplitChar;