Is there StartsWith or Contains in t sql with vari

问题:

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');

回答1:

StartsWith: left(@edition, 15) = 'Express Edition' or charindex('Express Edition', @edition) = 1.
Contains: charindex('Express Edition', @edition) >= 1

Examples:

Using left function:

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

or starting with SQL Server 2012, you can use iif function:

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

or using charindex function:

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

回答2:

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)

回答3:

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