CODEWITHSUNDEEP
sql-server-2008
isree
isree

Reputation: 53

How can use the result of case statements in to another manipulation?

select 'Test '+m.testname+' '+         
    Case
        When m.value = 'ttttt' Then 'tt'
        When m.styleName = 'ppppp' Then 'pp'
        When m.styleName = 'qqqqq' Then 'qq'
        When m.styleName = 'yyyyyy' Then 'yy'           
        Else ''
    End     from testtable m   where m.id=10'

This is my query, i need to check the length of string formed after case statement.if the length greater than 35 then i want to remove charecters from "m.testname" field. Thanks for your help.

Upvotes: 1

Views: 35

Answers (1)

Lamak
Lamak

Reputation: 70648

You can use your current query as a derived table:

select  YourString, 
        LEN(YourString) [Length],
        Case 
            When LEN(YourString) > 35 THEN REPLACE(YourString,testname,'')
            Else YourString
        End YourNewString
from (  select 'Test '+m.testname+' '+         
            Case
                When m.value = 'ttttt' Then 'tt'
                When m.styleName = 'ppppp' Then 'pp'
                When m.styleName = 'qqqqq' Then 'qq'
                When m.styleName = 'yyyyyy' Then 'yy'           
                Else ''
            End YourString,
            m.testname
        from testtable m   
        where m.id=10) a
;

Or you can use a CTE:

WITH CTE AS
(
    select 'Test '+m.testname+' '+         
        Case
            When m.value = 'ttttt' Then 'tt'
            When m.styleName = 'ppppp' Then 'pp'
            When m.styleName = 'qqqqq' Then 'qq'
            When m.styleName = 'yyyyyy' Then 'yy'           
            Else ''
        End YourString,
        m.testname
    from testtable m   
    where m.id=10
)
select  YourString, 
        LEN(YourString) [Length],
        Case 
            When LEN(YourString) > 35 THEN REPLACE(YourString,testname,'')
            Else YourString
        End YourNewString
from CTE;

Upvotes: 1

Related Questions