CODEWITHSUNDEEP
sqlsql-serverwildcard
Amsel
Amsel

Reputation: 107

Ignore wildcard in MSSQL Variable?

I have a stored procedure which includes two parameters. Prefix varchar(50) and Suffix varchar(50). The stored procedure selects some rows with a where clausle. the problem is now, if there is a wildcard in the variable i get something wrong. How do i ignore variable characters which could also be wildcards?

Example:

Lets say i have two rows:

1 . myColumn : 'ABCTTAX'
2. myColumn: 'ABCTT_X'

declare @Prefix varchar(50)
declare @Suffix varchar(50)

set @Prefix = 'ABC';
set @Suffix = '_X';

Select * from myTable where @Suffix IS NOT NULL AND myColumn like @Prefix + '__' + @Suffix

In the example above, both rows will match. but i want only the second one to match.

Upvotes: 0

Views: 1466

Answers (2)

MatrixTXT
MatrixTXT

Reputation: 2502

Actually you can do something like, Try

Select * from myTable where @Suffix IS NOT NULL AND myColumn like @Prefix + '%\' + @Suffix escape '\'

Upvotes: 0

EzLo
EzLo

Reputation: 14189

Escape the underscore literal by replacing it with brackets.

Select * from myTable where @Suffix IS NOT NULL AND myColumn 
    like REPLACE(@Prefix, '_', '[_]') + '__' + REPLACE(@Suffix, '_', '[_]')

This patch won't allow prefixes and suffixes to input wildcard underscores.

Upvotes: 2

Related Questions