Solved

How to check a value between two text columns?

Posted on 2015-02-11
16
64 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
  • 9
  • 5
  • 2
16 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
And the second one, change ISNULL to IS NULL
0
 

Author Comment

by:JOLEEJJ
Comment Utility
Sorry, the above query returned nothing (:
0
 
LVL 33

Expert Comment

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

Author Comment

by:JOLEEJJ
Comment Utility
I meant
WHERE COALESCE(BeginBarcode, '') >= 'B0000233' AND  COALESCE(EndBarcode, '') <= 'B0000233'
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:JOLEEJJ
Comment Utility
the is null shows error:

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

Expert Comment

by:Mike Eghtebas
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
Hi PortletPaul,

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

Great...

Joe
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to get previous Data from SQL 34 89
Problem with SqlConnection 5 109
SQL 2012 Syntax Error 5 23
Distinct values from two tables 14 17
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now