CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Gleb
Gleb

Reputation: 1432

Set variable value from case statement

I need to count all '0' in the string after coma if value is smaller then 1. Example: I have string '0,005', the result will be 2.

Thats works fine:

declare @s varchar(15), @i int
Set @s='0,05'
Set @i=Len(Substring(@s, 3, Len(@s)-3));
print @i

But if I do so, I have an error:

declare @s varchar(15), @i int
Set @s='0,05'
select
  case
    when CAST(@s as decimal)<1 then set @i= Len(Substring(@s, 3, Len(@s)-3))
  end

Error is "Incorrect syntax near keyword 'Set'". What should I do?

Upvotes: 2

Views: 6479

Answers (4)

David
David

Reputation: 11

declare @s varchar(15), @i int
set @s = '0,05'
set @i = case
           when @s like '0,%' then patindex('%[1-9]%', substring(@s, 3, len(@s) - 2)) - 1
           else 0
         end
select @i

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44356

The other cloned answers will give a wrong result if you use value '0,011'. There will also be an issue converting to decimal. This should return the correct result.

DECLARE @s varchar(15), @i int
Set @s='0,05'
SET @i = case when @s like '%[1-9]%,%' or @s not like '%,%[1-9]%' then 0
              else PatIndex('%[1-9]%',stuff(@s, 1, charindex(',', @s), '')) - 1
         end
SELECT @i

Upvotes: 1

mohan111
mohan111

Reputation: 8865

Declare @s varchar(15), @i int
Set @s='0,05'
SET @i = case when LEN(@s) > 1 then Len(Substring(@s, 3, Len(@s)-3)) End
PRINT @i

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28423

Case Statement Should be like below:

SET @i = CASE WHEN codition here Then result1 Else result2 END

And How can you CAST 0,05 into Numeric? It' not possible I think.

Like Below:

Declare @s varchar(15), @i int
Set @s='0,05'
SET @i = case when CAST(@s as decimal) < 1 then Len(Substring(@s, 3, Len(@s)-3)) End
                   ^^^^^^^^^^^^^^^^^^^ --Invalid Cast

Upvotes: 1

Related Questions