Menu
I've got a sproc (MSSQL 2k5) that will take a variable for a LIKE claus like so:
DECLARE @SearchLetter2 char(1) SET @SearchLetter = 't' SET @SearchLetter2 = @SearchLetter + '%' SELECT * FROM BrandNames WHERE [Name] LIKE @SearchLetter2 and IsVisible = 1 --WHERE [Name] LIKE 't%' and IsVisible = 1 ORDER BY [Name] Unfortunately, the line currently running throws a syntax error, while the commented where clause runs just fine. Can anyone help me get the un-commented line working?
271
It's ok if we want to limit the SELECT by this way. But if it's not intentional way of doing it could return incorrect results from planned ( Like "all Names begining with Test1..." ). Show activity on this post. Show activity on this post.
Using the CONCAT() function, we can work with user variables in LIKE clause. The syntax is as follows. set @anyVariableName='anyValue'; select yourColumnName1,yourColumnName2,yourColumnName3,...
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
If you are using a Stored Procedure:
ALTER PROCEDURE <Name> ( @PartialName VARCHAR(50) = NULL ) SELECT Name FROM <table> WHERE Name LIKE '%' + @PartialName + '%' Joel is it that @SearchLetter hasn't been declared yet? Also the length of @SearchLetter2 isn't long enough for 't%'. Try a varchar of a longer length.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
