Is there StartsWith or Contains in t sql with variables?

Question

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

score 172 · Accepted Answer · edited Feb 22 '19 at 15:40

172

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

edited Feb 22 '19 at 15:40

CroMagnon

1,218
7
20
32

answered Feb 29 '12 at 05:08

Kirill Polishchuk

54,804
11
122
125

4

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

score 77 · Answer 2 · answered Feb 29 '12 at 05:12

77

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)

answered Feb 29 '12 at 05:12

Gary.S

7,076
1
26
36

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

score 64 · Answer 3 · edited Apr 16 '15 at 07:53

64

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

edited Apr 16 '15 at 07:53

Valamas

24,169
25
107
177

answered Mar 25 '15 at 09:47

Thomas Koelle

3,416
2
23
44

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
2

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
14

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
Beware of this when there might be special characters in the like-string, e. g. '['. One might have to add escaping, but even then it seems to behave differently in different circumstances. – Andreas Reiff Oct 28 '22 at 12:36

score 4 · Answer 4 · edited Nov 02 '22 at 22:33

Here is the explanation of what you can do while filtering the data - in the end, you just pass the variable in a place where you need it.

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

Is there StartsWith or Contains in t sql with variables?

4 Answers4

Linked