112

I am trying to detect if the server is running Express Edition.

I have the following t sql.

DECLARE @edition varchar(50); 
set @edition = cast((select SERVERPROPERTY ('edition')) as varchar)

print @edition

In my instance, @edition = Express Edition (64-bit)

How can I do the following? (C# inspired).

DECLARE @isExpress bit;
set @isExpress = @edition.StartsWith('Express Edition');
Kirill Polishchuk
  • 52,773
  • 10
  • 120
  • 121
Valamas
  • 23,171
  • 24
  • 104
  • 174

3 Answers3

147

StartsWith

a) left(@edition, 15) = 'Express Edition'
b) charindex('Express Edition', @edition) = 1

Contains

charindex('Express Edition', @edition) >= 1

Examples

left function

set @isExpress = case when left(@edition, 15) = 'Express Edition' then 1 else 0 end

iif function (starting with SQL Server 2012)

set @isExpress = iif(left(@edition, 15) = 'Express Edition', 1, 0);

charindex function

set @isExpress = iif(charindex('Express Edition', @edition) = 1, 1, 0);
CroMagnon
  • 1,206
  • 7
  • 20
  • 32
Kirill Polishchuk
  • 52,773
  • 10
  • 120
  • 121
  • 3
    Note that internally these work differently, especially w.r.t. performance and its use of indexes. For example, a query using `colName LIKE 'prefix%'` will be very fast when `colName` is indexed, but `colName LIKE '%substring%'` or `colName LIKE '%suffix'` will be slow because SQL Server does not create suffix-trees when indexing text. Similarly using `LEFT` with a column will also be slow because those queries are not SARGable. SARGability is important: https://dba.stackexchange.com/questions/162263/what-does-the-word-sargable-really-mean – Dai Feb 14 '20 at 06:09
  • I would recommend to test the LIKE 'x%' method mentioned below. In some cases it is much faster – tonysepia Jun 24 '20 at 21:04
  • 1
    Logically I would avoid charindex.. I would assume char index will search the entire string to find the first chacacter, then check for the second character etc. Eg it won't realize that you are not interesed if the string starts at position 2 or greater – mark d drake Apr 07 '21 at 18:49
75

It seems like what you want is http://msdn.microsoft.com/en-us/library/ms186323.aspx.

In your example it would be (starts with):

set @isExpress = (CharIndex('Express Edition', @edition) = 1)

Or contains

set @isExpress = (CharIndex('Express Edition', @edition) >= 1)
Gary.S
  • 7,001
  • 1
  • 25
  • 35
  • Why not use LIKE `Express Edition%` ? Also, doesn't your solution return true of "Express Edition" is anywhere in the string? So it's more specifically just a "Contains" sort of way of doing it – Don Cheadle Feb 15 '16 at 19:41
  • 3
    @mmcrae While using the like version below would be fine this was the first way that came to mind and looks most like what the OP had. Also, the first expression will only return true if the variable starts with (charindex returns 1) the given argument. The second expression is a contains as it will return true if the argument is found anywhere in the string (charindex returns 1 or greater). – Gary.S Feb 17 '16 at 04:40
58

I would use

like 'Express Edition%'

Example:

DECLARE @edition varchar(50); 
set @edition = cast((select SERVERPROPERTY ('edition')) as varchar)

DECLARE @isExpress bit
if @edition like 'Express Edition%'
    set @isExpress = 1;
else
    set @isExpress = 0;

print @isExpress
Valamas
  • 23,171
  • 24
  • 104
  • 174
Thomas Koelle
  • 3,132
  • 2
  • 22
  • 35
  • If you read the question, it is not a query. Try my example with your answer and then please update your answer showing it works. – Valamas Apr 15 '15 at 20:39
  • 1
    You can use like in if-statements in t-sql. This is why I use like. I don't see why you want to give me minus points for that but why not. – Thomas Koelle Apr 16 '15 at 06:53
  • 13
    This should be the preferred way; it's more elegant, less verbose and "SQL aligned": as it uses the standard SQL LIKE operator, I should not need to read the documentation to understand it! – Fer García May 22 '15 at 00:23