Solved

How to check a value between two text columns?

Posted on 2015-02-11
16
70 Views
Last Modified: 2015-02-11
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
0
Comment
Question by:JOLEEJJ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
16 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40604708
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
 

Author Comment

by:JOLEEJJ
ID: 40604733
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40604735
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40604747
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40604751
And the second one, change ISNULL to IS NULL
0
 

Author Comment

by:JOLEEJJ
ID: 40604761
Sorry, the above query returned nothing (:
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40604766
both without returns? I am building a sample to try first.
0
 

Author Comment

by:JOLEEJJ
ID: 40604768
I meant
WHERE COALESCE(BeginBarcode, '') >= 'B0000233' AND  COALESCE(EndBarcode, '') <= 'B0000233'
0
 

Author Comment

by:JOLEEJJ
ID: 40604770
the is null shows error:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'IS'.
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40604787
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40604793
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40604797
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40604804
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40604813
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40604819
Joe writes The between operator >= and <= ... not working.

But after I tested it (WHERE BeginBarcode <= 'B0000233' AND  EndBarcode >= 'B0000233') it worked fine.
0
 

Author Closing Comment

by:JOLEEJJ
ID: 40604820
Hi PortletPaul,

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

Great...

Joe
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question