case statement in sybase
<p>I have following requirement for Sybase Query:-</p>

<p>Exclude below if:</p>

<ul>
<li>The SSN has more or less than nine digits</li>
<li>The SSN includes non-numeric characters</li>
<li>The SSN is blank</li>
<li>The SSN includes the same digits (for example, 000000000, 111111111 or 999999999)</li>
<li>The SSN has a 9 as the first digit and a 7, 8 or 9 as the fourth digit</li>
<li>The fourth and the fifth digits are 00 (except 800-00-0000)</li>
<li>The sixth through ninth digits are 0000</li>
</ul>

<p>I wrote case statement as below but it's not working, can someone help please:</p>

<pre><code>SELECT CASE
    WHEN LEN(a.MEMBER_SSN) &gt; 9 THEN ' '
    WHEN LEN(a.MEMBER_SSN) &lt; 9 THEN ' '
    WHEN LTRIM(RTRIM(a.MEMBER_SSN)) like '%[A-Z,a-z]%' then ' '
    WHEN LTRIM(RTRIM(WHEN a.MEMBER_SSN)) like '%[0-9]%' then ' '
    WHEN LTRIM(RTRIM(a.MEMBER_SSN)) in ( '000000000','000000001','000000002','000000003','000000004','000000005','999999999','111111111','000000070','123456789','999999998','000000071','888888888', ) THEN ''
    WHEN LTRIM(RTRIM(a.MEMBER_SSN)) NOT LIKE '9__[789]%' THEN a.MEMBER_SSN ELSE ' '
    WHEN LTRIM(RTRIM(a.MEMBER_SSN)) NOT LIKE '____[00]%' THEN a.MEMBER_SSN ELSE ' '
    WHEN LTRIM(RTRIM(a.MEMBER_SSN)) NOT LIKE '_____[0000]%' THEN a.MEMBER_SSN ELSE ' '
    END AS SUBSCRIBER_SSN From dbo.MEMBER_TABLE a
</code></pre>