CODEWITHSUNDEEP
sqlsql-server
Mogli
Mogli

Reputation: 2012

Error in > or < symbol in sql?

My code is :

DECLARE @String VARCHAR(100)
SET @String = 'Before 91-150 days'

-- Chop off the end character
SET @String = 
      CASE @String 
         WHEN null THEN null 
         ELSE (CASE LEN(@String) 
                  WHEN 0 THEN @String           
                  WHEN (charindex('-',(SUBSTRING(@String, charindex(' ', @String), CHARINDEX(' ', @String) - 3))) > 0) THEN --(shows error in >)
                        SUBSTRING(@String, charindex(' ', @String), CHARINDEX(' ', @String) - 4) - 1 
                  WHEN (charindex('-', SUBSTRING(@String, charindex(' ', @String), CHARINDEX(' ', @String) - 3)) < 0) THEN --(shows error in <)
                        SUBSTRING(@String, charindex(' ', @String), CHARINDEX(' ', @String) - 3) - 1
               END) 
      END

SELECT @String

Error is :

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '>'.

I don't know what I am doing wrong...

Thanks in advance.

Upvotes: 2

Views: 83

Answers (3)

IVNSTN
IVNSTN

Reputation: 9299

Your code lacks substrings/replacements

SUBSTRING(@String, charindex(' ', @String), CHARINDEX(' ', @String) - 4)

You don't use '-' position here. First and second CHARINDEX will point to the same ' ' (position 7) - that's why this code makes totally no sense at all

SUBSTRING ( expression ,start , length )
http://msdn.microsoft.com/en-us/library/ms187748.aspx

You could fix it by code which

  1. at first takes substring from first space till the end of string,
  2. takes substring of the string obtained in stage 1 from the beginning of it till the first space (second space in original string given)
  3. searches for '-' delimiter and uses devided string parts

so your code needs at least two substring.

try this:

DECLARE @String VARCHAR(100)
SET @String = 'Before 91-150 days'


SET @String = LTRIM(REPLACE(@String, 'before', ''))

SELECT @String

IF CHARINDEX('-', @String) > 0
BEGIN
    SET @String = LEFT(@String, CHARINDEX('-', @String)-1)
    SELECT @String

    SELECT TRY_CAST(@String AS INT)-1
END

or

DECLARE @String VARCHAR(100)
SET @String = 'Before 91-150 days'

SELECT 
    CASE
        WHEN CHARINDEX('-', LTRIM(REPLACE(@String, 'before', ''))) > 0
        THEN TRY_CAST(LEFT(LTRIM(REPLACE(@String, 'before', '')), CHARINDEX('-',  LTRIM(REPLACE(@String, 'before', '')))-1) AS INT)-1
        ELSE @String
    END

both with prints:

GO
PRINT 'test-1'

DECLARE @String VARCHAR(100)
SET @String = 'Before 91-150 days'


SET @String = LTRIM(REPLACE(@String, 'before', ''))

PRINT @String

IF CHARINDEX('-', @String) > 0
BEGIN
    SET @String = LEFT(@String, CHARINDEX('-', @String)-1)
    PRINT @String

    PRINT TRY_CAST(@String AS INT)-1
END
PRINT '==='

GO
PRINT 'test-2'

DECLARE @String VARCHAR(100)
SET @String = 'Before 91-150 days'

PRINT 
    CASE
        WHEN CHARINDEX('-', LTRIM(REPLACE(@String, 'before', ''))) > 0
        THEN TRY_CAST(LEFT(LTRIM(REPLACE(@String, 'before', '')), CHARINDEX('-',  LTRIM(REPLACE(@String, 'before', '')))-1) AS INT)-1
        ELSE @String
    END 

PRINT '==='
GO

output:

test-1
91-150 days
91
90
===
test-2
90
===

Upvotes: 1

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with the below query.

DECLARE @String VARCHAR(100)
SET @String = 'Before 91-150 days'

-- Chop off the end character
SET @String = 
      CASE  WHEN @String IS NULL 
            THEN null 
            WHEN LEN(@String)= 0 
            THEN @String          
            WHEN (charindex('-',(SUBSTRING(@String,charindex(' ',@String),CHARINDEX(' ',@String)-3))) > 0) 
            THEN SUBSTRING(@String,charindex(' ',@String),CHARINDEX(' ',@String)-4) -1 
            WHEN (charindex('-',SUBSTRING(@String,charindex(' ',@String),CHARINDEX(' ',@String)-3)) < 0) 
            THEN SUBSTRING(@String,charindex(' ',@String),CHARINDEX(' ',@String)-3) -1
       END 


SELECT @String

Upvotes: 0

DavidG
DavidG

Reputation: 118977

Your cases don't make sense. The first one is when 0 but the second one is a boolean. Make them both use an int by moving the first LEN inside the WHEN:

CASE 
    WHEN LEN(@String) = 0 
        THEN @String          
    WHEN CHARINDEX('-', SUBSTRING(@String, charindex(' ',@String), CHARINDEX(' ',@String)-3), 0) > 0
        THEN --Other stuff

Upvotes: 3

Related Questions