August 22, 2016 at 10:30 am
hi I have a issue with the like operator in the loop
if I set the like operator to LIKE '[@a-b]%'
when execute the SP with the parameter @a = a I got this in my wanado table
ContactName
Antonio del Valle Saavedra
Anne Heikkonen
Beate Vileid
if I set the like operator to LIKE '[@a-@b]%'
when execute the SP with the parameters @a = a and @b-2 = b I got this in my wanado table
ContactName
Beate Vileid
So what do I wrong ? in the code ??
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SupplierStats] Script Date: 22-08-2016 18:01:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SupplierStats]
-- ALTER PROC SupplierStats
@a nvarchar(50),
@b-2 nvarchar(50)
AS
BEGIN
DECLARE @imax INT,
@i INT
DECLARE @Contact VARCHAR(100),
@Company VARCHAR(50)
DECLARE @CompanyInfo TABLE(
RowID INT IDENTITY ( 1 , 1 ),
CompanyName VARCHAR(100),
ContactName VARCHAR(50)
)
INSERT @CompanyInfo
SELECT CompanyName,
ContactName
FROM Suppliers
WHERE ContactName LIKE '[@a-@b]%'
ORDER BY CompanyName
SET @imax = @@ROWCOUNT
SET @i = 1
WHILE (@i <= @imax)
BEGIN
SELECT @Contact = ContactName,
@Company = CompanyName
FROM @CompanyInfo
WHERE RowID = @i
------------------------------------------------------
-- INSERT PROCESSING HERE
INSERT INTO dbo.wanado (ContactName, CompanyName)
VALUES (@Contact, @Company);
------------------------------------------------------
PRINT CONVERT(varchar,@i)+' Contact: ' + @Contact + ' at ' + @Company
SET @i = @i + 1
END -- WHILE
END -- SPROC
August 22, 2016 at 11:04 am
You're probably looking for something like this:
ALTER PROC [dbo].[SupplierStats]
@a nvarchar(50),
@b-2 nvarchar(50)
AS
SET NOCOUNT ON;
INSERT INTO dbo.wanado(
ContactName,
CompanyName)
SELECT CompanyName,
ContactName
FROM Suppliers
WHERE ContactName LIKE @a + '%'
OR ContactName LIKE @b-2 + '%'
ORDER BY CompanyName;
August 22, 2016 at 11:30 am
To follow up on Luis' fine answer, the reason you're seeing those results is that you're not actually using the parameters you're passing.
When you do this:
[column] LIKE '[@a-b]%'
you're saying to return rows where the string in that column begins with the literal character '@', or any character in the range from 'a' to 'b'.
When you do this:
[column] LIKE '[@a-@b]%'
you're saying to return rows where the string in that column begins with the literal character '@', any character in the range from 'a' to '@' (which is nothing), or a 'b'.
To actually use the parameters you pass in you'll need to do something like Luis did.
Keep in mind that the way Luis wrote it will work for this particular example because the range of starting characters is only the two characters you passed in, but if you actually want to return any name starting with a character in a range that might be more than those two characters (say, with @a='b' and @b-2='q'), then you'll need to write it differently.
Something like this:
[column] LIKE '['+@a+'-'+@b+']%'
Cheers!
August 22, 2016 at 11:51 am
And if you're looking for a range of values between the two parameters, it becomes a bit tricky. For instance, if your parameter values are 'A' and 'C', you'll get anything beginning with an 'A' or anything beginning with a 'C', but nothing beginning with a 'B'.
There are two basic options: ignore anything that starts with @b-2, but isn't equal to @b-2 or adjust your @b-2 parameter either manually or programmatically to extend the range to include anything that starts with @B.
-- Ignore names after @b-2 even if they start with @b-2
WHERE ContactName BETWEEN @a and @b-2
-- programmatically update @b-2 to get the proper range.
DECLARE @a VARCHAR(50) = 'UVW',
@b-2 VARCHAR(50) = 'XYZ'
SELECT @b-2 = ISNULL(STUFF(@b, v.ind, LEN(@b), CHAR(ASCII(SUBSTRING(@b, v.ind, 1)) + 1)), REPLICATE('ÿ', 50))
FROM ( VALUES(LEN(@b) + 1 - PATINDEX('%[^ÿ]%', REVERSE(@b))) ) v(ind)
CROSS APPLY ( VALUES(SUBSTRING(@b, v.ind, 1)) ) l(letter)
<your query here>
WHERE ContactName >= @a
AND ContactName < @b-2
The second version looks for the last non-ÿ character in @b-2 and "adds one" to it and truncates the rest of the string. NOTE: I have not tested this with other collations, and do not guarantee that this works with anything other than the default collation.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2016 at 12:00 pm
Jacob Wilkins (8/22/2016)
Something like this:
[column] LIKE '['+@a+'-'+@b+']%'Cheers!
This will only work if you pass in single characters as your parameters. Given that you've defined your parameters as VARCHAR(50), it seems unlikely that that is the expected use of those parameters. If you use this approach with parameters longer than one character, you will generally not get the results that you expect.
For example, what would you expect from @a = 'Zona' and @b-2 = 'Zook'? Would you be surprised that it includes 'Albright'?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2016 at 12:17 pm
drew.allen (8/22/2016)
Jacob Wilkins (8/22/2016)
Something like this:
[column] LIKE '['+@a+'-'+@b+']%'Cheers!
This will only work if you pass in single characters as your parameters. Given that you've defined your parameters as VARCHAR(50), it seems unlikely that that is the expected use of those parameters. If you use this approach with parameters longer than one character, you will generally not get the results that you expect.
For example, what would you expect from @a = 'Zona' and @b-2 = 'Zook'? Would you be surprised that it includes 'Albright'?
Drew
Agreed.
His example only used single characters, and since he was using the characters to match wildcard that specifies single characters to match, I assumed that was the requirement.
It of course must change if you're looking for a BETWEEN that acts as a LIKE 'something%' for the string at the end of the range.
I would not be surprised at that particular result, since the resulting LIKE is saying to return anything that begins with a 'Z','o','n',anything in the range 'a' to 'Z','o','o',or 'k'.
Cheers!
August 22, 2016 at 12:23 pm
My post is marked as an answer. But I still would like some feedback to make sure that you understand how and why the code was simplified.
Also that you understand the remarks made by Jacob and Drew.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply