Replace extraneous characters

[jjenright]

Hello!

I need to find extraneous characters in a field and remove all of them. I want to create a new field that will contain the field with all extraneous characters removed. I was given the following code but it does not work:

SELECT VIEW_SPECIAL_MAIN.ITEM
REPLACE (VIEW_SPECIAL_MAIN.ITEM,"-"),
REPLACE (VIEW_SPECIAL_MAIN.ITEM,"."),
REPLACE (VIEW_SPECIAL_MAIN.ITEM," "),
REPLACE (VIEW_SPECIAL_MAIN.ITEM,"/"),
REPLACE (VIEW_SPECIAL_MAIN.ITEM,"\"),
REPLACE (VIEW_SPECIAL_MAIN.ITEM,"("),
REPLACE (VIEW_SPECIAL_MAIN.ITEM,")"),
IIF((MID(VIEW_SPECIAL_MAIN.ITEM,1)="X"),(MID(VIEW_SPECIAL_MAIN.ITEM,2)) )
FROM VIEW_SPECIAL_MAIN;

Thanks

 

[bborissov]

UPDATE VIEW_SPECIAL_MAIN
       SET ITEM = 
       REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(
       CASE WHEN LEFT(ITEM,1) = 'X'
                 THEN SUBSTRING(ITEM,2,5000)
                 ELSE ITEM END
       '-','')
       ,'.','')
       ,' ','')
       ,'/','')
       ,'\','')
       ,'(','')
       ,')','')

not tested


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jjenright]

Thank You Borislav Borissov

I worked with the code you provided and tested the following whish works :

UPDATE LAWSON.VIEW_SPECIALS_MAIN
SET VEN_ITEM =
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
'-','')
,'.','')
,' ','')
,'/','')
,'\','')
,'(','')
,')','')

I want to create and update a new field in a view that will contain the 'stripped' ven_item, removing 'extraneous' characters. I also want to remove a leading 'X'. I need to keep the original value in the existing field and update the new field with the stripped data.

I need to add the following code to the view to remove a leading "X" :

CASE WHEN SUBSTR(VEN_ITEM,1) = 'X'
THEN SUBSTRING(VEN_ITEM,2,5000)
ELSE VEN_ITEM END

How do I accomplish adding a new field with the 'stripped' data (removing all extraneous characters including leading 'X's, keeping the original value in the existing field ?

Thank You.