CODEWITHSUNDEEP
sql-servert-sqlconditional-statements
Ariel
Ariel

Reputation: 153

How to select using Left Function with a condition in SQL Server?

In postgres I could use the following:

SELECT LEFT([COLUMN], 1) = 'B'
FROM [DB].[dbo].[TABLE]

But in SQL Server doesn't work the use of "="

How can make this works it out in SQL Server.

Upvotes: 1

Views: 10639

Answers (2)

Hansjp
Hansjp

Reputation: 66

OP asked about a postgresql syntax which is really odd in the eyes of sql server. I guess you want to return a column of boolean values? Then the problem would be that sql server does not have a boolean datatype altogether! But you can use BIT and assign meaning to 0, 1 and NULL. It is not built-in however.

As for the headline "How to select using Left Function with a condition in SQL Server?", it is dead simple: CASE-END statement supports LEFT function. There is one caveat, however, which is the two different syntaxes of CASE-END:

Syntax 1:

CASE [Column] WHEN 'B' THEN 1 ELSE 0 END

Syntax 2:

CASE WHEN [Column] = 'B' THEN 1 ELSE 0 END

The big difference between the two is: Syntax 1 make all conditions check against the same value (i.e. [Column]), whereas syntax 2 support differing condition clauses. And they can't be mixed. For example: 1) 

CASE LEFT([Column],1) WHEN 'B' THEN 1 WHEN LEFT([Column],2) = 'Ca' THEN 2 ELSE 0 END

throw a syntax error on the "=", while 2)

CASE WHEN LEFT([Column],1) = 'B' THEN 1 WHEN LEFT([Column],2) = 'Ca' THEN 2 ELSE 0 END

works perfectly.

Upvotes: 1

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

Reputation: 93724

Nothing wrong with LEFT function but to return true or false after evaluating the expression you need CASE or IIF statement in SQL server 

case when LEFT([COLUMN], 1) = 'B' then 'true' else 'false' end

or

IIF(LEFT([COLUMN], 1) = 'B','true','false' )

Upvotes: 3

Related Questions