Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    Unanswered: "not equal" condition statement issues

    (Using SQL Server 2005)

    Prepare to cringe: I'm brand new to SQL and am working on my third scalar-valued function.

    I'm trying to strip all semicolons [;] and greater than signs [>] from a string, but for some reason the following condition is evaluating as FALSE pretty much no matter what input is provided. (@Counter is a counter running from 1 to the length of @Victim; the idea is to look at each character and remove it if it's a semicolon or greater than sign.)

    Code:
    ((SUBSTRING(@Victim, @Counter, 1) <> ';') AND (SUBSTRING(@Victim, @Counter, 1) <> '>'))
    Any clues what I'm doing wrong?

    Full code:

    Code:
    ALTER FUNCTION dbo.ScrubDenialCode
    	(@Victim as varchar(255))
    RETURNS varchar(255)
    AS
    	BEGIN
    		DECLARE @Counter As Integer
    		DECLARE @DenialCode As varchar(255)
    		DECLARE @DenialCode2 As varchar(255)
    		DECLARE @ScrubbedVictim As varchar(255)
    		DECLARE @ScrubDenialCode As varchar(255)
    		
    		SELECT @Counter = 1
    		WHILE @Counter <= Len(@Victim)
    			BEGIN
    				IF ((SUBSTRING(@Victim, @Counter, 1) <> ';') AND (SUBSTRING(@Victim, @Counter, 1) <> '>')) 
    					BEGIN
    						SELECT @ScrubbedVictim = @ScrubbedVictim + SUBSTRING(@Victim, @Counter, 1)
    					END
    				SELECT @Counter = @Counter + 1
    			END
    		SELECT @Victim = @ScrubbedVictim
    
    		If Len(@Victim) = 0 Or @Victim IS NULL Or CONVERT(int, Len(@Victim) / 3) <> Len(@Victim) / 3 
    		    BEGIN
    				SELECT @ScrubDenialCode = ''
    			END
    		ELSE
    			BEGIN
    				SELECT @DenialCode = Left(@Victim, 3)
    				If Len(@Victim) > 3 --CHECK FOR MULTIPLE DENIAL CODES
    					BEGIN
    						SELECT @Counter = 1
    						WHILE @Counter <= Len(@Victim) / 3 - 1
    							BEGIN
    								SELECT @DenialCode2 = SUBSTRING(@Victim, 3 * @Counter + 1, 3)
    								If @DenialCode2 <> @DenialCode 
    									BEGIN
    										If @DenialCode2 <> 'OVB' 
    											BEGIN
    												If @DenialCode = 'OVB' 
    													BEGIN
    														SELECT @DenialCode = @DenialCode2 --REPLACE OVB WITH NEW CODE
    													END
    												ELSE
    													BEGIN
    														SELECT @ScrubDenialCode = ''
    														GoTo THEEND
    													END
    											END
    									END
    							END
    						SELECT @ScrubDenialCode = @DenialCode
    						SELECT @Counter = @Counter + 1
    					END
    				ELSE
    					BEGIN
    						SELECT @ScrubDenialCode = @DenialCode
    					END
    			END			    
    		
    THEEND:
    	RETURN @Victim
    	--RETURN @ScrubDenialCode
    	END

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What you're doing wrong is not using the REPLACE() function.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Umm...
    Code:
    select replace (replace (@victim, ';', ''), '>', '')
    Does that do what you needed?

  4. #4
    Join Date
    Nov 2012
    Posts
    2
    Thanks, guys - that worked (with one slight correction):

    Code:
    select @victim = replace (replace (@victim, ';', ''), '>', '')
    I would still be curious to know what's wrong with the conditional statement, however. I have a feeling I'll have legitimate need for this sort of statement in the future.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •