if condtion is not giving expected result. not sure why?

Question

Although i found the answer to my query about applying regex for zipcode. However I was just building my own function as below and I stuck in one code line which confusing me and I am not sure what I am doing wrong.

I am expecting below condtion to fail and expecting an error.

When I am running the code the below condition  failing and I am not expecting it to fail. I am not sure why it is failing?

  or LEN(@applicantZip)=10 AND ((LEFT(@applicantzip,6)<>'-') or  LEFT(@applicantzip,6)<>'')

DECLARE @applicantZip varchar (10) --varchar
		DECLARE @ZIP SMALLINT 
	
SET @ZIP=0
set @applicantzip='11235-6171' --another to test is '11235 1432'



Select (LEFT(@applicantzip,6)) AS 'First 6 char'
SELECT LEN(@APPLICANTZIP) AS LENGTH
			if LEN(@applicantZip)=10 --Expecting space or hyphen in value.
			--sitatuion when invalid zip as -1234567890  or 123456789- . removing
			--hyphen wouldnt be sufficent.  ISNUMERcIC function wouldnt work for-,.+,\. So creating own function.
			
			
			BEGIN
			
			    IF LEN(REPLACE(LEFT(@applicantzip,5),'-',''))<5 
			    OR LEN(REPLACE(LEFT(@applicantzip,5),'+',''))<5
			    or LEN(REPLACE(LEFT(@applicantzip,5),'\',''))<5
			    or LEN(REPLACE(LEFT(@applicantzip,5),'.',''))<5
			    --for last 4
			    or LEN(REPLACE(RIGHT(@applicantzip,4),'-',''))<4 
			    OR LEN(REPLACE(RIGHT(@applicantzip,4),'+',''))<4
			    or LEN(REPLACE(RIGHT(@applicantzip,4),'\',''))<4
			    or LEN(REPLACE(RIGHT(@applicantzip,4),'.',''))<4
			    --for the value after 5th digit. 
			    or LEN(@applicantZip)=10 AND ((LEFT(@applicantzip,6)<>'-') or  LEFT(@applicantzip,6)<>'')
			    
			    
			    BEGIN
			     SET @zip=0
			     select 'oho'
			     return 
			    END
			  END

---

i am a novice and a student

Answer 1

I do run the codes and have got no problem. What is the expected error?

---

Many Thanks & Best Regards, Hua Min

Answer 2

I didn't follow any previous thread you have, but this looks like slightly odd:

  or LEN(@applicantZip)=10 AND ((LEFT(@applicantzip,6)<>'-') or  LEFT(@applicantzip,6)<>'')

You first check that the zip code has 10 characters, and then you check whether the first six characters are equal to a hyphen or an empty string. This condition would be true for a value like:

 '-         ABCD'

That is a hyphen followed by five spaces and then there are some characters at the end.

I would guess that you want to look at the sixth letter. To that end you would say:

  substring(@applicantzip, 6, 1) <> '-'

Also, I'm not really sure what you are doing, but this form of advanced string processing is better done in a language like C#, so why not make this a CLR function?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

OH i had some doubt on the below code and I ca see now I wanted to put the replace function there which is missing. ironically code did still run without the proper coding.

			    or LEN(@applicantZip)=10 AND ((LEFT(@applicantzip,6)<>'-') or  LEFT(@applicantzip,6)<>'')

---

i am a novice and a student