Hi all, I need write a stored proc with a clause like "between like xxx AND
like xxx" so, I can't. Thats is, a stored proc that returns values between
two parameters that use wildcards (for instance "LIKE @.date").
Any Ideas. I read all the books and, I encounter information about BETWEEN
usage and LIKE usage, but not how to use BOTH in the same SP.
Thanks an avanceNando_uy wrote:
> Hi all, I need write a stored proc with a clause like "between like xxx AN
D
> like xxx" so, I can't. Thats is, a stored proc that returns values between
> two parameters that use wildcards (for instance "LIKE @.date").
> Any Ideas. I read all the books and, I encounter information about BETWEEN
> usage and LIKE usage, but not how to use BOTH in the same SP.
> Thanks an avance
Depends on what your LIKE clause is and what the desired result is. It
seems to me that "between" two wildcards would only make sense for a
limited number of cases. For example:
CREATE tbl (col VARCHAR(10) NOT NULL, ...);
SELECT col FROM tbl
WHERE col LIKE '[a-c]%';
could be rewritten as:
..
WHERE col >= 'a' AND col < 'd';
but this is obviously different from:
..
WHERE col BETWEEN 'a' AND 'c';
Could you give a better explanation of what you want to achieve please.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Can you post the query. Seems very interesting.|||hi all, the query seems like this:
ALTER PROCEDURE dbo.Prueba_Entre
(@.vI nchar(100),
@.vF nchar(100))
AS SELECT Codigo_TSA, Id_Producto, Nombre_Emp, Ingresos, Egresos
FROM dbo.Vista_Prueba
WHERE (Codigo_TSA BETWEEN @.vI AND @.vF)
I want to substitute the parameters @.vI, @.vF for something like this:
LIKE @.vI AND LIKE @.vI, since I need that the parameters accept wildcards (%)
.
I currently use SQL server 2000 and, for simplicity use ACCESS 2003 to
graphically design the SP.
In other words, I need to retrieve values between two parameters, both MUST
accept wildcards, It's possible?
"Omnibuzz" wrote:
> Can you post the query. Seems very interesting.|||Thanks for the input.
But we need to know the functionality behind using the like operator.
Because from my experience, I would say it is logically wrong in any
situation to use like and between in the following cases
(pseudo code, of course :)
col1 between like '%abc%' and like '%xyz%'
of course if you want it to be compared between something like
'%abc' and '%xyz'
or
'abc%' and 'xyz%'
then you can use something like this in the where condition.
left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
left operator in this case
or
right(col1,3) between 'abc' and 'xyz'
Boy.. lack of info leads to lots of typing :)
Hope this helps.|||thanks for you time, the case is: I have a product table, I want to retrieve
a range of these by product code, ie: 10xxxx to 25xxx, because I want the
user have the facility to write ONLY the first characters of product code to
establish the range
I it's possible?
Thanks
"Omnibuzz" wrote:
> Thanks for the input.
> But we need to know the functionality behind using the like operator.
> Because from my experience, I would say it is logically wrong in any
> situation to use like and between in the following cases
> (pseudo code, of course :)
> col1 between like '%abc%' and like '%xyz%'
> of course if you want it to be compared between something like
> '%abc' and '%xyz'
> or
> 'abc%' and 'xyz%'
> then you can use something like this in the where condition.
> left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
> left operator in this case
> or
> right(col1,3) between 'abc' and 'xyz'
> Boy.. lack of info leads to lots of typing :)
> Hope this helps.
>
>|||Is it an integer column or a char column (or does the column hold integer
values only?).
And do they always enter the first 2 digits? or it can be 1 or 3 digits?
"Nando_uy" wrote:
> thanks for you time, the case is: I have a product table, I want to retrie
ve
> a range of these by product code, ie: 10xxxx to 25xxx, because I want the
> user have the facility to write ONLY the first characters of product code
to
> establish the range
> I it's possible?
> Thanks
> "Omnibuzz" wrote:
>|||thanks Omnibuzz, it's a character column and until now I don't decide the
numbers of characters the user input, but say, 2 characters will be fine!!!
Looks better?
"Omnibuzz" wrote:
> Is it an integer column or a char column (or does the column hold integer
> values only?).
> And do they always enter the first 2 digits? or it can be 1 or 3 digits?
>
> "Nando_uy" wrote:
>|||Anyways.. check this out..
create table #temp( a varchar(10))
insert into #temp
select '12ewew'
union all
select '13rere'
union all
select '23dds'
union all
select '26rerere'
union all
select '2454cdfd'
select * from #temp where a between '12' and '25'
Hope this helps.|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Depends on what your LIKE clause is and what the desired result is. It
> seems to me that "between" two wildcards would only make sense for a
> limited number of cases. For example:
> CREATE tbl (col VARCHAR(10) NOT NULL, ...);
> SELECT col FROM tbl
> WHERE col LIKE '[a-c]%';
> could be rewritten as:
> ...
> WHERE col >= 'a' AND col < 'd';
Maybe, but it would not necessarily generate the same result:
CREATE TABLE #tmp (a varchar(19) COLLATE Finnish_Swedish_CS_AS NOT NULL)
go
INSERT #tmp(a) VALUES ('Annichen')
INSERT #tmp(a) VALUES ('avfallskvarn')
INSERT #tmp(a) VALUES ('Beatrice')
INSERT #tmp(a) VALUES ('betongarbetare')
INSERT #tmp(a) VALUES ('Cecilia')
INSERT #tmp(a) VALUES ('citrusfrukt')
INSERT #tmp(a) VALUES ('Daneiella')
INSERT #tmp(a) VALUES ('daggfuktig')
go
SELECT a FROM #tmp WHERE a LIKE '[a-c]%' -- five rows
SELECT a FROM #tmp WHERE a >= 'a' AND a < 'd' -- six rows
go
DROP TABLE #tmp
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
