|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Between clause...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 avance Nando_uy wrote:
> Hi all, I need write a stored proc with a clause like "between like xxx AND Depends on what your LIKE clause is and what the desired result is. It> 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 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 -- David Portas (REMOVE_BEFORE_REPLYING_dpor***@acm.org) writes:
> Depends on what your LIKE clause is and what the desired result is. It Maybe, but it would not necessarily generate the same result:> 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'; 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, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Is it me, or is that a bug?
Case insensitive should be case insensitive. Here it appears to be sometimes case sensitive and sometimes not. What's worse, like '[a-c]%' retrieves anything starting with a, A, b, B, c, but not C? How does SQL Server come up with this? If it used ASCII characters, then a-c would include a,b, and c only. Coincidently, LIKE '[a-C]%' seems to work fine. Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns97CAA9BA3FD21Yazorman@127.0.0.1... > David Portas (REMOVE_BEFORE_REPLYING_dpor***@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, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Jim Underwood wrote:
> Is it me, or is that a bug? Case-sensitivity is determined by the collation and Erland specified a> > Case insensitive should be case insensitive. case-sensitive one. He's quite right. My reply did assume case-insensitive, which is not necessarily what the OP wanted. -- 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 -- But on my case insensitive database, the results would appear inconsistent.
You and I, I believe, had the same results. Why would like '[a-c]%' Return 'a', 'A', 'b', 'B', 'c' - but exclude 'C'? It is treating the a and b as case insensitive, but not the C. Unless SQL Server thinks the alphabet is aAbBcCdD.. etc. And yes, I know this depends on the collation, but it seems totally inconsistent to me. If the results from the above criteria included 'C', or excluded 'A', 'B', then I would think it was consistent. Including two and excluding the third... this just puzzles me. Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1148323988.833309.209980@u72g2000cwu.googlegroups.com... > Jim Underwood wrote: > > Is it me, or is that a bug? > > > > Case insensitive should be case insensitive. > > Case-sensitivity is determined by the collation and Erland specified a > case-sensitive one. He's quite right. My reply did assume > case-insensitive, which is not necessarily what the OP wanted. > > -- > 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 > -- > On Mon, 22 May 2006 11:44:14 -0400, Jim Underwood wrote:
(snip) > What's worse, Hi Jim,>like '[a-c]%' retrieves anything starting with a, A, b, B, c, but not C? >How does SQL Server come up with this? If it used ASCII characters, then >a-c would include a,b, and c only. It doesn't use ASCII. It uses the collation specified for the column in the CREATE TABLE statement (Finnish_Swedish_CS_AS). Apparently, lowercase characters come befoore upppercase characters in this collation - the order appears to be a - A - b - B - c - C - etc. -- Hugo Kornelis, SQL Server MVP Ahhhh....
I missed that in the create table statement. I was too focused on the select to notice the DDL was specifying the collation. I just assumed that my default collation was being used. Thanks for pointing that out. Show quote "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message news:054472pqqnrqo863svq5pup15pj2i3o0sm@4ax.com... > On Mon, 22 May 2006 11:44:14 -0400, Jim Underwood wrote: > > (snip) > > What's worse, > >like '[a-c]%' retrieves anything starting with a, A, b, B, c, but not C? > >How does SQL Server come up with this? If it used ASCII characters, then > >a-c would include a,b, and c only. > > Hi Jim, > > It doesn't use ASCII. It uses the collation specified for the column in > the CREATE TABLE statement (Finnish_Swedish_CS_AS). > Apparently, lowercase characters come befoore upppercase characters in > this collation - the order appears to be a - A - b - B - c - C - etc. > > -- > Hugo Kornelis, SQL Server MVP 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? Show quote "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 Show quote "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? Show quote "Nando_uy" wrote: > 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. > > > > > > > > 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? Show quote "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: > > > 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. > > > > > > > > > > > > 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. There is one problem here...
If you have a value '25 Front Street", the value will not be returned, because it is greater then '25'. The lower range values work fine, but the upper range values do not. To adjust for this, you either have to concatenate one or more characters (i.e. 'Z') to the upper end, or use the left function, as you suggested earlier. Or, is there a way to specify different handling of the alpha sort? Show quote "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message news:9EE80531-80D8-4AD5-BEE1-3082334C8876@microsoft.com... > 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. we can may be use
select * from #temp where a >= '12' and a < '26' Show quote "Jim Underwood" wrote: > There is one problem here... > If you have a value '25 Front Street", the value will not be returned, > because it is greater then '25'. > > The lower range values work fine, but the upper range values do not. To > adjust for this, you either have to concatenate one or more characters (i.e. > 'Z') to the upper end, or use the left function, as you suggested earlier. > > Or, is there a way to specify different handling of the alpha sort? > > "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message > news:9EE80531-80D8-4AD5-BEE1-3082334C8876@microsoft.com... > > 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. > > >  |
|||||||||||||||||||||||
Other interesting topics