Is there StartsWith or Contains in t sql with variables?

我试图检测服务器是否正在运行 ExpressEdition。

我有以下的 t 平方。

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


print @edition

在我的例子中，@edition = Express Edition (64-bit)

我该怎么做呢? (C # 启发)。

DECLARE @isExpress bit;
set @isExpress = @edition.StartsWith('Express Edition');
175670 次浏览
小开
最佳答案

从

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

包含

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

例子

函数

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

函数(从 SQLServer2012开始)

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

函数

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

看起来你想要的是 http://msdn.microsoft.com/en-us/library/ms186323.aspx。

在你的例子中，它是(开头) :

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

或者包含

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

我会用

like 'Express Edition%'

例如:

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
小开

下面解释了在过滤数据时可以做些什么——最后，只需将变量传递到需要它的地方。

WHERE CustomerName LIKE 'a%'
--Finds any values that start with "a"


WHERE CustomerName LIKE '%a'
--Finds any values that end with "a"


WHERE CustomerName LIKE '%or%'
--Finds any values that have "or" in any position


WHERE CustomerName LIKE '_r%'
--Finds any values that have "r" in the second position


WHERE CustomerName LIKE 'a__%'
--Finds any values that start with "a" and are at least 3 characters in length


WHERE ContactName LIKE 'a%o'
--Finds any values that start with "a" and ends with "o"


SELECT * FROM my_table WHERE upper(my_column) LIKE 'SEARCHED%';
--Starts with, case insensitive


SELECT * FROM my_table WHERE upper(my_column) LIKE '%SEARCHED';
--Ends with, case insensitive


SELECT * FROM my_table WHERE upper(my_column) LIKE '%SEARCHED%';
--Contains, case insensitive

提交答案