CODEWITHSUNDEEP
sql-server-2008-r2
sam
sam

Reputation: 1304

How to write a condition inside select statement in SQL Server 2008 R2

My Scenario is I have the following table -

FullName                 ManufacturingCost
------------------------------------------
Harry Singh Malhotra          $200.00
Harry                         $200.00
Danny                         $200.00
Britany Krout                 $100.00

Now I want two columns from the above table - fullName and FirstName. I am using this query:

Select 
    fullName, left(fullName, charindex(' ', fullName, 1) - 1) as firstName
from 
    Table1

The problem in the above query is that in the second and the third row Firstname is returned as Null because charindex could not find ' ' in these rows.

I want the result as:

    FullName                 FirstName
    -------------------------------------
    Harry Singh Malhotra     Harry
    Harry                    Harry
    Danny                    Danny
    Britany Krout            Britany

Can someone help me in letting me know how to add conditions inside the statement 

select * from Table11

because I want to check a condition for each value in a particular column.

Select case does not work because it takes only scalar values.

Upvotes: 0

Views: 211

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Use Case Statement to handle FullName with no Middlename or LastName

SELECT fullName,
       First_Name=LEFT(( fullName ), CASE
                                       WHEN Charindex(' ', fullName) = 0 THEN Len(fullName)
                                       ELSE Charindex(' ', fullName)
                                     END)
FROM   Table1

ParseName trick will also work but your string should have at max 4 words.

SELECT fullName,
       First_Name=Reverse(Parsename(Replace(Reverse(fullName), ' ', '.'), 1))
FROM   table1

Upvotes: 2

Related Questions