How to check a value between two text columns?

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 ''.

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'.

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

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.

And the second one, change ISNULL to IS NULL

Sorry, the above query returned nothing (:

both without returns? I am building a sample to try first.

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

the is null shows error:

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

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'

Have I misunderstood something, just looks like use of between in the where clause to me.

This result:

| RECID | BEGINBARCODE | ENDBARCODE |
|-------|--------------|------------|
|     4 |     B0000231 |   B0000250 |

Select allOpen in new window

from this query:

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

Select allOpen 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')
;

Select allOpen in new window

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.

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

Joe writes The between operator >= and <= ... not working.

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

Hi PortletPaul,

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

Great...

Joe