display values between Two Varchar data types columns

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

my table contain below data

VB

I want to dispaly 2014-1026 to 2014-1030 data

Posted 12-Feb-15 1:14am

Member 10229796

Updated 12-Feb-15 1:19am

v2

Add a Solution

Comments

CHill60 12-Feb-15 7:21am

What have you tried?

Member 10229796 12-Feb-15 7:27am

select ES_LIBRARY_ID as ES_BAR_CODE from es_library_user where ES_LIBRARY_ID IS NOT NULL and ES_LIBRARY_ID between '2014-100' and '2014-1029'

CHill60 12-Feb-15 7:34am

And what is wrong with that - except that you need to have between '2014-1026' and '2014-1030'

Member 10229796 12-Feb-15 7:42am

if give between '2013-100' and '2013-102'
it display like
2014-1000
2014-1010
2014-1011
2013-1012
2013-1013
2014-1016
2014-1017
2014-1018
2014-1019

Tomas Takac 12-Feb-15 7:35am

In your query you use different values than in the question. But generally between should work with 2014-1026 and 2014-1030. Not sure about '2014-100' and '2014-1029'.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-02-12T01:43:00

Your select works.
If I create a new table:

And use your select:

SQL

SELECT * FROM Table_1
WHERE MyColumn IS NOT NULL AND MyColumn BETWEEN '2014-1026' AND '2014-1030'

Then I get what you are asking for:

C#

MyColumn
2014-1026 
2014-1027 
2014-1028 
2014-1030 
2014-1029

If you want to exclude '2014-1029' because the earlier values include greater values, then you will probably need additional info, as SQL doesn't normally work on row numbers, and (unless you specify a sort order explicitly with an ORDER BY clause) can return rows in any order it finds convenient: so you can;t guarantee that the order of rows you show in your example data will be preserved unless there is a different column on which to order.

/\jmot · Answer 2 · 2015-02-12T01:37:00

Solution 2

Try..

SQL

select * from Table_Name where (SUBSTRING(barcode,1,4)>=2014 and SUBSTRING(barcode,6,4)<=1026) or
(SUBSTRING(barcode,1,4)>=2014 and SUBSTRING(barcode,6,4)<=1030)

Posted 12-Feb-15 1:37am

/\jmot

Comments

/\jmot 12-Feb-15 8:20am

hahaha..
somebody just downvote my answer??
will you please say me, why?? :)

CHill60 12-Feb-15 8:26am

Couple of thoughts as to why ... it's slightly over-engineered - BETWEEN would be a better route (see OriginalGriff's answer). And you are comparing a varchar from the substring with a numeric without casting - whereas it probably works it's not good to rely on implicit conversions. Finally - should that "or" be an "and"?

/\jmot 12-Feb-15 10:46am

thanks,for your answer .
but i think he's just want to compare the two part of the string value separated by a '-' sign,that's why i post it.
YES, you are right i forgot to cast those two string to Integer value.

btw, thank you so much. :)

Shweta N Mishra · Answer 3 · 2015-02-12T02:22:00

Solution 1

SQL

select
    * 
from BarCodeTable 
where
  Convert(int,Replace((barcode,'-',''))>=20141026
  and Convert(int,Replace((barcode,'-',''))<=20141030

Posted 12-Feb-15 2:22am

Shweta N Mishra

Comments

/\jmot 12-Feb-15 8:24am

this is not gonna work??
where is the logic??
you just cross everything..

Shweta N Mishra 12-Feb-15 8:40am

What i crossed ?

did you tried the code

check this

Create table #Barcode(Barcode Varchar(100))

insert #Barcode select '2014-1024'
insert #Barcode select '2014-1025'
insert #Barcode select '2014-1026'
insert #Barcode select '2014-1027'
insert #Barcode select '2014-1028'
insert #Barcode select '2014-1030'
insert #Barcode select '2014-1031'
insert #Barcode select '2014-1029'

select
*
from #Barcode
where
Convert(int,Replace(barcode,'-',''))>=20141026
and Convert(int,Replace(barcode,'-',''))<=20141030

display values between Two Varchar data types columns

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us