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?
This question is related to
sql-server
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.
But in my opinion one important thing.
The "char(number)" it's lenght of variable.
If we've got table with "Names" like for example [Test1..Test200] and we declare char(5) in SELECT like:
DECLARE @variable char(5)
SET @variable = 'Test1%'
SELECT * FROM table WHERE Name like @variable
the result will be only - "Test1"! (char(5) - 5 chars in lenght; Test11 is 6 )
The rest of potential interested data like [Test11..Test200] will not be returned in the result.
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..." ).
In my opinion if we don't know the precise lenght of a SELECTed value, a better solution could be something like this one:
DECLARE @variable varchar(max)
SET @variable = 'Test1%'
SELECT * FROM <table> WHERE variable1 like @variable
This returns (Test1 but also Test11..Test19 and Test100..Test199).
If you are using a Stored Procedure:
ALTER PROCEDURE <Name>
(
@PartialName VARCHAR(50) = NULL
)
SELECT Name
FROM <table>
WHERE Name LIKE '%' + @PartialName + '%'
As Andrew Brower says, but adding a trim
ALTER PROCEDURE <Name>
(
@PartialName VARCHAR(50) = NULL
)
SELECT Name
FROM <table>
WHERE Name LIKE '%' + LTRIM(RTRIM(@PartialName)) + '%'
This works for me on the Northwind sample DB, note that SearchLetter has 2 characters to it and SearchLetter also has to be declared for this to run:
declare @SearchLetter2 char(2)
declare @SearchLetter char(1)
Set @SearchLetter = 'A'
Set @SearchLetter2 = @SearchLetter+'%'
select * from Customers where ContactName like @SearchLetter2 and Region='WY'
We can write directly too...
DECLARE @SearchLetter CHAR(1)
SET @SearchLetter = 'A'
SELECT *
FROM CUSTOMERS
WHERE CONTACTNAME LIKE @SearchLetter + '%'
AND REGION = 'WY'
or the following way as well if we have to append all the search characters then,
DECLARE @SearchLetter CHAR(1)
SET @SearchLetter = 'A' + '%'
SELECT *
FROM CUSTOMERS
WHERE CONTACTNAME LIKE @SearchLetter
AND REGION = 'WY'
Both these will work
I had also problem using local variables in LIKE.
Important is to know: how long is variable.
Below, ORDER_NO is 50 characters long, so You can not use: LIKE @ORDER_NO, because in the end will be spaces.
You need to trim right side of the variable first.
Like this:
DECLARE @ORDER_NO char(50)
SELECT @ORDER_NO = 'OR/201910/0012%'
SELECT * FROM orders WHERE ord_no LIKE RTRIM(@ORDER_NO)
It may be as simple as LIKE '%%[%3]%%' being [%3] the input variable.
This works for me with SAP B1 9.1
DECLARE @SearchLetter2 char(1)
Set this to a longer char.
It may be as simple as LIKE '%%[%3]%%' being [%3] the input variable.
This works for me with SAP B1 9.1
I had also problem using local variables in LIKE.
Important is to know: how long is variable.
Below, ORDER_NO is 50 characters long, so You can not use: LIKE @ORDER_NO, because in the end will be spaces.
You need to trim right side of the variable first.
Like this:
DECLARE @ORDER_NO char(50)
SELECT @ORDER_NO = 'OR/201910/0012%'
SELECT * FROM orders WHERE ord_no LIKE RTRIM(@ORDER_NO)
But in my opinion one important thing.
The "char(number)" it's lenght of variable.
If we've got table with "Names" like for example [Test1..Test200] and we declare char(5) in SELECT like:
DECLARE @variable char(5)
SET @variable = 'Test1%'
SELECT * FROM table WHERE Name like @variable
the result will be only - "Test1"! (char(5) - 5 chars in lenght; Test11 is 6 )
The rest of potential interested data like [Test11..Test200] will not be returned in the result.
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..." ).
In my opinion if we don't know the precise lenght of a SELECTed value, a better solution could be something like this one:
DECLARE @variable varchar(max)
SET @variable = 'Test1%'
SELECT * FROM <table> WHERE variable1 like @variable
This returns (Test1 but also Test11..Test19 and Test100..Test199).
DECLARE @SearchLetter2 char(1)
Set this to a longer char.
As Andrew Brower says, but adding a trim
ALTER PROCEDURE <Name>
(
@PartialName VARCHAR(50) = NULL
)
SELECT Name
FROM <table>
WHERE Name LIKE '%' + LTRIM(RTRIM(@PartialName)) + '%'
If you are using a Stored Procedure:
ALTER PROCEDURE <Name>
(
@PartialName VARCHAR(50) = NULL
)
SELECT Name
FROM <table>
WHERE Name LIKE '%' + @PartialName + '%'
DECLARE @SearchLetter2 char(1)
Set this to a longer char.
This works for me on the Northwind sample DB, note that SearchLetter has 2 characters to it and SearchLetter also has to be declared for this to run:
declare @SearchLetter2 char(2)
declare @SearchLetter char(1)
Set @SearchLetter = 'A'
Set @SearchLetter2 = @SearchLetter+'%'
select * from Customers where ContactName like @SearchLetter2 and Region='WY'
This works for me on the Northwind sample DB, note that SearchLetter has 2 characters to it and SearchLetter also has to be declared for this to run:
declare @SearchLetter2 char(2)
declare @SearchLetter char(1)
Set @SearchLetter = 'A'
Set @SearchLetter2 = @SearchLetter+'%'
select * from Customers where ContactName like @SearchLetter2 and Region='WY'
This works for me on the Northwind sample DB, note that SearchLetter has 2 characters to it and SearchLetter also has to be declared for this to run:
declare @SearchLetter2 char(2)
declare @SearchLetter char(1)
Set @SearchLetter = 'A'
Set @SearchLetter2 = @SearchLetter+'%'
select * from Customers where ContactName like @SearchLetter2 and Region='WY'
We can write directly too...
DECLARE @SearchLetter CHAR(1)
SET @SearchLetter = 'A'
SELECT *
FROM CUSTOMERS
WHERE CONTACTNAME LIKE @SearchLetter + '%'
AND REGION = 'WY'
or the following way as well if we have to append all the search characters then,
DECLARE @SearchLetter CHAR(1)
SET @SearchLetter = 'A' + '%'
SELECT *
FROM CUSTOMERS
WHERE CONTACTNAME LIKE @SearchLetter
AND REGION = 'WY'
Both these will work
Source: Stackoverflow.com