Complex LIKE clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex LIKE clause

Author

Topic

eyehefbee
Starting Member

1 Post

Posted - 2010-07-27 : 15:57:15

I started this piece of work thinking it would be a nice challenge, however, it's more complex than I imagined and I'm stuck. I need to do a select based on a column which stored the days and times that meetings are held. Its has 21 characters so if a meeting is on a Monday morning it is YNNNNNNNNNNNNNNNNNNNNN, on a Monday afternoon NYNNNNNNNNNNNNNNNNNNN, Monday evening NNYNNNNNNNNNNNNNNNNNN. On a Sunday morning and evening it is NNNNNNNNNNNNNNNNNNYNY. I have no control over the data or how it is stored.

So the LIKE clause needs to take a day 1 to 7 (or zero for any day) and a time 1 to 3 (am, pm, evening) or zero for any time.

So the user may want day=0, time=3 for any evening. Or day=1, time=0 for any time on a Monday.

Is this possible in a single WHERE clause? Any help gratefully received.

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-27 : 17:02:16

[code]
DECLARE @n table (n int)
INSERT INTO @n(n)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL
SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18

SELECT *
FROM @n
WHERE n/3 = n/3.00

DECLARE @Meeting char(21)
-- M T W R F S S
-- 123456789012345678901
SET @Meeting = 'NNYNNNNNYNNNNNNNNNNNN' --Wednesday Evening

-- User wants day=0, time=3

SELECT *
FROM @n
JOIN (SELECT @Meeting AS Evening) AS XXX
ON SUBSTRING(Evening,n,1) = 'Y'
WHERE n/3 = n/3.00
[/code]

I had mushroom pizza for lunch

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-07-27 : 17:07:13

Something like this?

day=0, time=3

WHERE MyColumn LIKE '__Y_________________'

day=1, time=0

WHERE MyColumn LIKE '___Y________________'
OR MyColumn LIKE '____Y_______________'
OR MyColumn LIKE '_____Y______________'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-27 : 17:25:48

I was goofing around with some junk. Here is something that works, but not necessarily the most pretty. :)

-- Sample data
DECLARE @Meeting TABLE (MeetingID INT, MeetingPeriod CHAR(21))
INSERT @Meeting
SELECT 1, 'YNNNNNNNNNNNNNNNNNNNN' 
UNION ALL SELECT 2, 'NNNNNNNNNNNNNNNNNNNYN' 
UNION ALL SELECT 3, 'NYNYNNNNYNNNNNNNNNNNN' 

-- Declare variables
DECLARE @Day SMALLINT
DECLARE @Time SMALLINT

SET @Day = 2
SET @Time = 0

-- Get Results
SELECT *
FROM @Meeting AS M
WHERE
	CASE 
		--  @Day > 0 AND @Time > 0
		WHEN @Day > 0 AND @Time > 0
			AND SUBSTRING(MeetingPeriod, (@Day * 3) - 2, 3) LIKE '%Y%' 
			AND SUBSTRING(MeetingPeriod, (@Day * 3) - 2 + (@Time - 1), 1) LIKE '%Y%'
			THEN 1
		-- @Day = 0 AND @Time = 0	
		WHEN @Day = 0 AND @Time = 0 AND MeetingPeriod LIKE '%Y%' 
			THEN 1 
		-- @Day > 0 AND @Time = 0
		WHEN @Day > 0 AND @Time = 0
			AND SUBSTRING(MeetingPeriod, (@Day * 3) - 2, 3) LIKE '%Y%' 
			THEN 1			
		--Day = 0 AND @Time > 0
		WHEN @Day = 0 AND @Time > 0 
			AND
			( 
				-- Since there are only 7 days we can check each one
				SUBSTRING(MeetingPeriod, (1 + @Time - 1), 1) LIKE '%Y%' 
				OR SUBSTRING(MeetingPeriod, (4 + @Time - 1), 1) LIKE '%Y%' 
				OR SUBSTRING(MeetingPeriod, (7 + @Time - 1), 1) LIKE '%Y%' 
				OR SUBSTRING(MeetingPeriod, (10 + @Time - 1), 1) LIKE '%Y%' 
				OR SUBSTRING(MeetingPeriod, (13 + @Time - 1), 1) LIKE '%Y%' 
				OR SUBSTRING(MeetingPeriod, (16 + @Time - 1), 1) LIKE '%Y%' 
				OR SUBSTRING(MeetingPeriod, (19 + @Time - 1), 1) LIKE '%Y%' 
			)
			THEN 1
		ELSE
			0
	END = 1

EDIT: Removed a couple of superfluous checks.

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-27 : 17:27:48

Was mine too simple?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-27 : 17:30:28

quote:
Originally posted by X002548

Was mine too simple?

Simple, yes. But it doesn't meet the requirements as I understand them.

Kristen
Test

22859 Posts

Posted - 2010-07-27 : 17:33:49

I thought yours was good Brett.

But I think you are getting old as in the old days I'm sure you would have delivered a lecture on Normalising ...

... and explained how using SUBSTRING in the JOIN would adersely effect performance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-27 : 18:29:17

quote:
Originally posted by X002548

I had mushroom pizza for lunch

You crack me up.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources