SQL Code Samples - SQL  - SQL_FIND - Find and Remove a specific character from a string 

Author:		George Pearson 

Company:	Green Springs Software, Inc.   www.green-springs.com

Purpose:	SQL Code Samples for finding and removing any single character from
		a character string.  Uses SQL (VB and iSeries compatable)

Components:	*None

Parameters:     N/A	

Written:	2003	 


	2 Examples to remove an embedded dash ("-") or any specific character from a text string.  
	Each example shows a Select and an Update statement.  The Select will display 
	the Before field data, position of the dash, and the after image.  The Update 
	statement will update the field without the dash.  Each run of the Update will 
	only remove one occurance per field, so you might need to run multiple times if 
	the field data contains multiple occurances.  These examples were written and tested 
	on an IBM iSeries (AS/400), but will work in most SQL environments.  
	
		
	EXAMPLE 1 = Requirement to remove all embedded dashes in the character field ABALPH.
	JDE World and JDE OneWorld users will recognize this field from the JDE Address Book 
	Master file, F0101. This field contained between 0 and many dashes, so I needed to run it 
	about 5 times until they were all converted.   Still faster than writing a program!

	
       	SELECT abalph, posstr(abalph,'-'), 
	 trim(substr(abalph,1,posstr(abalph,'-')-1))||' '||  
	 trim(substr(abalph,posstr(abalph,'-')+1, 40 - posstr(abalph,'-'))) 
   	 FROM f0101 WHERE abat1 IN ('C','V') and 
   	 posstr(abalph,'-') BETWEEN 1 and 39

       	UPDATE f0101 SET abalph = 
	 trim(substr(abalph,1,posstr(abalph,'-')-1))||' '|| 
	 trim(substr(abalph,posstr(abalph,'-')+1, 40 - posstr(abalph,'-'))) 
	 WHERE abat1 IN ('C','V') and posstr(abalph,'-') BETWEEN 1 and 39 
	 
	 
	EXAMPLE 2 = Requirement to remove all embedded dashes in the character field PRSS.  Infinium	
	users will recognize this field as the character version of Social Security Number 
	from the HR/PY Employee Root Master file, PRPMS. All records contain 2 dashes in PRSS, 
	so the Update SQL statement needed to be executed twice.
	
       	SELECT prss, posstr(prss,'-'),
	 trim(substr(prss,1,posstr(prss,'-')-1))||' '||               
	 trim(substr(prss,posstr(prss,'-')+1, 11 - posstr(prss,'-'))) 
	 FROM prpms WHERE prtedh= 0
	
	UPDATE prpms SET prss= 
	 trim(substr(prss,1,posstr(prss,'-')-1))||' '|| 
	 trim(substr(prss,posstr(prss,'-')+1, 11 - posstr(prss,'-'))) 
	 WHERE prtedh= 0 

      **********************************************************************************************