Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to check a value between two text columns?

Posted on 2015-02-11
16
Medium Priority
?
73 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 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 2000 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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Integration Management Part 2

972 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