CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
The Demigeek
The Demigeek

Reputation: 773

SQL Server Boolean Expression evaluation

I have a table that stores data from the county appraisal district. I want to add a computed column to tell me if the property is owner-occupied. I define "owner occupied" as true if the property address is the same as the owner address, false otherwise.

Because of data entry inaccuracies in the source data from the county, if I do a strict text comparison I get a lot of false non-owner-occupied results. So I want to test "If the property's street name is not in the owner's address, or if the property's address number is not in the owner's address, then this is a non-owner-occupied property"

I wrote the following:

alter table appriasaldata add 
    IsOwnerOccupied as 
     case
        ((charindex(locastreetnumber, owneraddress) = 0) or (charindex(locastreetname, owneraddress) = 0))
        when TRUE THEN 1 ELSE 0
     end

SQL Server doesn't like the = signs after the CHARINDEX functions. How can I rewrite this to be acceptable to SQL Server? (I'm using SQL Server 2005 if it matters.)

Upvotes: 7

Views: 20726

Answers (4)

Steven
Steven

Reputation: 1

Try this:

SELECT
    owneraddress,
    locastreetnumber,
    locastreetname,
    charindex(locastreetnumber, owneraddress),
    charindex(locastreetname,   owneraddress),
    CASE
        WHEN charindex(locastreetnumber,
                       owneraddress)
            +charindex(locastreetname,
                       owneraddress)    > 0
        THEN 1
        ELSE 0
    END
FROM @appriasaldata

Upvotes: -1

Rob Garrison
Rob Garrison

Reputation: 7234

I came up with the same thing as Shannon Severance.

That code is a proper computed column.

Test the idea with this simple code:

DECLARE @appriasaldata TABLE (
    owneraddress        varchar(100),
    locastreetnumber    varchar(100),
    locastreetname      varchar(100)
)

INSERT INTO @appriasaldata (
    owneraddress,
    locastreetnumber,
    locastreetname
)
VALUES (
    '2701 SW Vaughn Street, Portland, OR',
    '2701',
    'SW Vaughn Street'
)

SELECT
    owneraddress,
    locastreetnumber,
    locastreetname,
    charindex(locastreetnumber, owneraddress),
    charindex(locastreetname,   owneraddress),
    CASE
        WHEN charindex(locastreetnumber, owneraddress) <> 0
          or charindex(locastreetname,   owneraddress) <> 0
        THEN 1
        ELSE 0
    END
FROM @appriasaldata

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294187

You cannot put boolean expression in a case lookup list. Unlike C type languages, in T-SQL a boolean cannot be compared against other values. And last but not least Transact-SQL does not have TRUE and FALSE.

So you need to move the boolean expression into the case WHEN, which expectes a boolean:

alter table appriasaldata add 
    IsOwnerOccupied as 
     case when
        (charindex(locastreetnumber, owneraddress) = 0) 
       or (charindex(locastreetname, owneraddress) = 0)
         THEN 1 ELSE 0
     end

Upvotes: 2

Shannon Severance
Shannon Severance

Reputation: 18410

Expressions can not return true or false in SQL Server. (No boolean type) But you can move the entire expression inside the when like:

alter table appriasaldata add 
    IsOwnerOccupied as 
     case when ((charindex(locastreetnumber, owneraddress) = 0) 
          or (charindex(locastreetname, owneraddress) = 0))
        THEN 1 ELSE 0
     end

Upvotes: 9

Related Questions