locked
How to filter the data which are matching the condition RRS feed

  • Question

  • User361793021 posted

    Hi all,

    I have a table called table1 and below given sample data in the table

    table1

    Red Fight

    Black BUS

    White Flight

    Red Bus

    i want to write a query in such a way that i have to get output as, for Flight FT and for Bus BS

    O/P

    FT

    BS

    FT

    BS

    select
    
    case
    
    when column like '% Flight' then 'FT'
    
    when column like '% Bus' then 'BS'
    
    else ''
    
    END from table1


    Please suggest me a solution.

    Thanks,

    Vishal

    Friday, July 29, 2016 4:21 AM

Answers

  • User669412048 posted

    You need little modification:

    select
    case
    when myColumn like '%Flight%' then 'FT'
    when myColumn like '%Bus%' then 'BS'
    else ''
    END from @table1

    Complete example:

    DECLARE @table1 TABLE
    (
    myColumn Nvarchar(50)	
    )
    
    INSERT INTO @table1 values ('Red Flight')
    INSERT INTO @table1 values ('Black BUS')
    INSERT INTO @table1 values ('White Flight')
    INSERT INTO @table1 values ('Red Bus')
    
    select
    case
    when myColumn like '%Flight%' then 'FT'
    when myColumn like '%Bus%' then 'BS'
    else ''
    END from @table1
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2016 4:36 AM

All replies

  • User669412048 posted

    You need little modification:

    select
    case
    when myColumn like '%Flight%' then 'FT'
    when myColumn like '%Bus%' then 'BS'
    else ''
    END from @table1

    Complete example:

    DECLARE @table1 TABLE
    (
    myColumn Nvarchar(50)	
    )
    
    INSERT INTO @table1 values ('Red Flight')
    INSERT INTO @table1 values ('Black BUS')
    INSERT INTO @table1 values ('White Flight')
    INSERT INTO @table1 values ('Red Bus')
    
    select
    case
    when myColumn like '%Flight%' then 'FT'
    when myColumn like '%Bus%' then 'BS'
    else ''
    END from @table1
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 29, 2016 4:36 AM
  • User-1404113929 posted

    hi,

    can you try this code.

    select
    
    case
    
    when column like '%Flight%' then 'FT'
    
    when column like '%Bus%' then 'BS'
    
    else ''
    
    END from table1

     thanks,

    murali

    Friday, July 29, 2016 6:52 AM