Solved

How to check a value between two text columns?

Posted on 2015-02-11
16
65 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
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 33

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
 
LVL 33

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 33

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 33

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 33

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 48

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 33

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 33

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 48

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 33

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
In or Between 2 44
Max Consumption Rate (MCR) 3 34
Managing Columnstore Indexes 2 19
ORA-00923: FROM keyword not found where expected 3 38
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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 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

20 Experts available now in Live!

Get 1:1 Help Now