Is There StartsWith or Contains in T-SQL with Variables?

Exploring the Depths: Going Beyond the Basics

Wildcards: The Heart and Soul of LIKE Statement

You will find the % wildcard putting in the hard yards when using the LIKE operator:

• LIKE 'Data%' matches "Database", "DataScience", and anything that starts with "Data".
• LIKE 'Data_' is handy for matching "Data1", "Datax", but not "Data12"
• LIKE 'Data%Center_' has the flexibility to match "DataCenter1" or "DataCenterA", giving flexibility a whole new meaning!

Coping with Case Sensitivity

Not everything is capital, so use UPPER() or LOWER() for consistent results:

SELECT * FROM Table WHERE UPPER(Column) LIKE UPPER(@Term + '%');


Different Data Types: Cast Away!

When interacting with functions like SERVERPROPERTY, which do not sing the "string" tune:

DECLARE @VersionName NVARCHAR(256) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(256));


Switching Gears: CHARINDEX for StartsWith

CHARINDEX, the LIKE alternative that knows exactly where to start looking for a substring:

DECLARE @isExpress BIT;
-- Checks if the string starts with 'Express Edition'
IF CHARINDEX('Express Edition', @VersionName) = 1 
    SET @isExpress = 1; -- Express Delivery!
ELSE
    SET @isExpress = 0;  -- Economy Shipment Recieved


Brevity is the Soul of IIF Function

For the fans of compact syntax, IIF function from SQL Server 2012 onwards is an absolute gem:

SET @isExpress = IIF(LEFT(@VersionName, 15) = 'Express Edition', 1, 0); -- Old is gold, but new is express!


Interacting with PATINDEX for Advanced Patterns

Meet PATINDEX, the next level in search functionality with a knack for pattern identification; Something regex fans would appreciate:

-- Find the starting position of any numeric digit sequence.
SELECT PATINDEX('%[0-9]%', Column) FROM Table; -- All numbers, appear!


Practical Use-Cases: From Corner Cases to Daily Tasks

Dynamic String Patterns: One String to Search Them All

Dynamic SQL: Gives you the flexibility you always wanted but remember, with great power comes greater responsibility!

SET @SQLCommand = 'SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''' + @SearchPattern + '%''';
EXEC sp_executeSQL @SQLCommand; -- The Magic Wand wave!


The Special Characters Dilemma

Escape special characters either with square brackets or the ESCAPE keyword:

-- Finding percent symbols in strings:
SELECT * FROM Table WHERE Column LIKE '%[%]%' ESCAPE '['; -- Percent-ception!


Multi-pattern Searches: More the Merrier

Wildcards can be pieced together to search for multi-pattern strings. A little bit of creativity goes a long way!

-- Match strings containing 'ing' followed by 'data' anywhere after:
SELECT * FROM Table WHERE Column LIKE '%ing%data%'; -- 'ing' the bell before 'data' enters.


Avoid the Pitfalls: Keeping an Eye on Efficiency

In enterprise-level databases, excessive use of wildcards or leading % in patterns might hinder performance. Indexed views or full-text indexing might be the saviors!