How to check a value between two text columns?

Hi

I need to find out a value that exists between two text columns that contains range of barcode numbers. In the following example, I need to return the recid if a barcode : "B0000233" is searched which actually in row 4.
The between operator >= and <= or fails to get this rowid, Can you please help me on this?

BeginBarcode      EndBarcode
B0000001      B0000010
B0000011      B0000210
B0000211      B0000230
B0000231      B0000250
B0000251      B0000270
B0000271      B0000470
B0000471      B0000490
B0000491      B0000590
B0000591      B0000690
B0000691      B0000790

Thanks
Joe
JOLEEJJAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
BETWEEN it is the syntactical equivalent of this:

select *
from table1
where (  'B0000233' >= BeginBarcode and  'B0000233' <= EndBarcode  )
;

If either, or both,  boundary values are absent then the result is indeterminate as there is no guidance about what value to provide as substitute. I would suggest/hope that both BeginBarcode & EndBarcode would be NOT NULL :)

I'm not sure why Joe has had a problem using between or the equivalent, I can only suggest that it may be something that hasn't been revealed to us yet.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
WHERE COALESCE(BeginBarcode, '') >= 'B0000233' AND  COALESCE(EndBarcode, '') <= 'B0000233'

Even if this solution works technically may not the best

try also:

WHERE (BeginBarcode >= 'B0000233' AND  EndBarcode<='B0000233') AND  (BeginBarcode NOT ISNULL AND EndBarcode NOT ISNULL)

This post has been revised

Next the author will write:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WHERE'.
Msg 105, Level 15, State 1, Line 7
Unclosed quotation mark after the character string ''.
0
 
JOLEEJJAuthor Commented:
The first one shows the following error message:

SELECT * FROM  BarcodeRange  WHERE
WHERE COALESCE(BeginBarcode, '') >= 'B0000233' AND  COALESCE(EndBarcode, '') <= '>= 'B0000233'

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WHERE'.
Msg 105, Level 15, State 1, Line 7
Unclosed quotation mark after the character string ''.

The second one show error like this:

SELECT * FROM  BarcodeRange   WHERE
(BeginBarcode >= 'B0000233' AND  EndBarcode<='B0000233') AND  (BeginBarcode NOT ISNULL AND EndBarcode NOT ISNULL)

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'ISNULL'.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Mike EghtebasDatabase and Application DeveloperCommented:
Hi JOLEEJJ,

I was supposed to test something on EE site. So please ignore the addition I made the the end of my last column. This will help EE admins to revise their site and stop this from happening. On your response, I will read your comments to see if I can handle them.

Thanks

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Please revise the first one to:

WHERE COALESCE(BeginBarcode, '') >= 'B0000233' AND  COALESCE(EndBarcode, '') <= 'B0000233'

You have used:

WHERE COALESCE(BeginBarcode, '') >= 'B0000233' AND  COALESCE(EndBarcode, '') <= '>= 'B0000233'

Note my first post says this post has been revised.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
And the second one, change ISNULL to IS NULL
0
 
JOLEEJJAuthor Commented:
Sorry, the above query returned nothing (:
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
both without returns? I am building a sample to try first.
0
 
JOLEEJJAuthor Commented:
I meant
WHERE COALESCE(BeginBarcode, '') >= 'B0000233' AND  COALESCE(EndBarcode, '') <= 'B0000233'
0
 
JOLEEJJAuthor Commented:
the is null shows error:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'IS'.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
This works

Select * From #T     -- change table name
WHERE BeginBarcode <= 'B0000233' AND  EndBarcode >= 'B0000233'

This also works:
WHERE COALESCE(BeginBarcode, '') <= 'B0000233' AND  COALESCE(EndBarcode, '') >= 'B0000233'
0
 
PortletPaulfreelancerCommented:
Have I misunderstood something, just looks like use of between in the where clause to me.

This result:
| RECID | BEGINBARCODE | ENDBARCODE |
|-------|--------------|------------|
|     4 |     B0000231 |   B0000250 |

Open in new window


from this query:
select *
from table1
where 'B0000233' between BeginBarcode and EndBarcode
;

Open in new window


from this table:
CREATE TABLE Table1
	(recid int identity primary key, [BeginBarcode] varchar(8), [EndBarcode] varchar(8))
;
	
INSERT INTO Table1
	([BeginBarcode], [EndBarcode])
VALUES
	('B0000001', 'B0000010'),
	('B0000011', 'B0000210'),
	('B0000211', 'B0000230'),
	('B0000231', 'B0000250'),
	('B0000251', 'B0000270'),
	('B0000271', 'B0000470'),
	('B0000471', 'B0000490'),
	('B0000491', 'B0000590'),
	('B0000591', 'B0000690'),
	('B0000691', 'B0000790')
;

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
I was trying to use my new found knowledge on COALESCE() thinking some field maybe null and causing the problem. Of course simple BETWEEN as you are pointing out is all needed.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Hi Paul,

Please see "Next the author will write:" in my first post. I did report this to Netminder this morning and he wanted some proof for it, I guess. I reproduce the problem here and sent the link to him.

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Joe writes The between operator >= and <= ... not working.

But after I tested it (WHERE BeginBarcode <= 'B0000233' AND  EndBarcode >= 'B0000233') it worked fine.
0
 
JOLEEJJAuthor Commented:
Hi PortletPaul,

Thanks for the help.
'B0000253' between BeginBarcode and EndBarcode
works fine.

Great...

Joe
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.