Strip special characters like [ ¿ · } ] 1

[Qmoto]

Hello Again,

I've got some special characters I need to strip out of a field but there are a few rows that do not seem to be affected.

Actually, I should say that I have it 90% working, but there are some records with a trailing curly bracket ( } ) that will not leave... *sigh*

Ok, here's a sample of the data I'm working with, and the code I'm using. What do I need to change in order to make this work?

SAMPLE DATA
   
   SHIPTO_KEY
   52
   100001
   ¿·11342}
   0000001
   ¿·14455N
   75       (Thats 75 w/ 5 trailing spaces)
   ¿·000077


CODE TO REMOVE SPECIAL CHARACTERS
SELECT
CASE
	WHEN LEFT(C6B9CD, 2) LIKE '¿·'	THEN REPLACE(C6B9CD, '¿·', '')
	WHEN RIGHT(C6B9CD, 1) LIKE '%}'	THEN REPLACE(C6B9CD, '}', '')
	ELSE C6B9CD
END AS SHIPTO_KEY

FROM
SHIPTO_KEY

ORDER BY
C6B9CD ASC

EXAMPLE OF DATA THAT SURVIVES MY QUERY
07070}
07404}
07391}

*sigh*

I'm soooo close.... what am I missing?

PS
The base field is CHAR(8)
The special characters can be typed using
ALT+168 (¿) and
ALT+250 (·)
The curly bracket is just the standard curly bracket :0)

Thanks for the help!


Merry Christmas!
Steve

 

[Qmoto]

I should also mention that removing the wildcard character (in blue) does not change the results.

CASE
    WHEN LEFT(C6B9CD, 2) LIKE '¿·'    THEN REPLACE(C6B9CD, '¿·', '')
    WHEN RIGHT(C6B9CD, 1) [COLOR=blue]LIKE '}'[/color]    THEN REPLACE(C6B9CD, '}', '')
    ELSE C6B9CD
END AS SHIPTO_KEY

 

[earthandfire]

Would the simpler:

SELECT
  REPLACE(REPLACE(C6B9CD, '¿·', ''), '}', '')
AS SHIPTO_KEY

FROM
SHIPTO_KEY

not work?


Hope this helps

 

[Qmoto]

Wow!

That was quick and simple!

... sad. I've been struggling with this all morning. *sigh* but my ordeal is now over.


Thanks a million!
[have a star]

 

[earthandfire]

Thanks for the star, and glad to have helped.