August 30, 2022 at 6:13 pm
How do I set up a query for the following example (where "HOTEL_A" is the table, and ROOM_NUMBER is the attribute, and the room numbers are all 3 digits) :
Select * from HOTEL_A
WHERE the first position of the 3 digit ROOM_NUMBER is not equal to 5
Thanks!
-BK
August 30, 2022 at 6:25 pm
WHERE ROOM_NUMBER <500 or ROOM_NUMBER >= 600
perhaps? If it's an INT. If it's a string, you could do something like this:
WHERE ROOM_NUMBER NOT LIKE '5%'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 31, 2022 at 3:28 pm
So with my Joe Celko hat on 🙂
Is Room Number a numeric value or not? Most people would say yes, but in fact I would say no. The reason being is that it contains TWO pieces of information: the Floor Number (an Integer) and the room number on the floor (assuming an identical floor layout) which indicates it's physical location on the floor (can be treated as an INT but isn't really...)
You can't do math on room numbers; you are more likely to want to do spatial analysis or aggregate by room location than the whole room number.
anyway....
Use FLOOR( ROOM_NUMBER / 100.00) and Modulus % to split the room number into Floor and room identifer.
Incidentally, if ROOM_NUMBER is in fact an int, you don't even need the FLOOR() just do ROOM_NUMBER / 100 because an INT divied by an INT will always give an INT.
DECLARE @Room_number INT = 575
select @Room_number / 100
select @Room_number % 100
September 1, 2022 at 3:10 am
So with my Joe Celko hat on 🙂
Is Room Number a numeric value or not? Most people would say yes, but in fact I would say no. The reason being is that it contains TWO pieces of information: the Floor Number (an Integer) and the room number on the floor (assuming an identical floor layout) which indicates it's physical location on the floor (can be treated as an INT but isn't really...) You can't do math on room numbers; you are more likely to want to do spatial analysis or aggregate by room location than the whole room number.
anyway....
Use FLOOR( ROOM_NUMBER / 100.00) and Modulus % to split the room number into Floor and room identifer.
Incidentally, if ROOM_NUMBER is in fact an int, you don't even need the FLOOR() just do ROOM_NUMBER / 100 because an INT divied by an INT will always give an INT.
DECLARE @Room_number INT = 575
select @Room_number / 100
select @Room_number % 100
Unfortunately, in this case, that would lead to non-SARGable criteria in a WHERE clause.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2022 at 7:39 am
Agreed, which is why it should be stored as two separate fields. Could do it on the table with a couple of computed columns.
September 1, 2022 at 7:55 am
Agreed, which is why it should be stored as two separate fields. Could do it on the table with a couple of computed columns.
Or do it the way I suggested.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 1, 2022 at 5:24 pm
Please follow the link in Jeff's signature and post some DDL and sample data to help us understand your structure and data.
September 1, 2022 at 7:11 pm
Agreed, which is why it should be stored as two separate fields. Could do it on the table with a couple of computed columns.
I guess I'd have to disagree, Aaron... reminds me of people that make the mistake of storing dates and times in separate columns and then going bananas when they need them as a single unit... especially on DATETIME2 datatypes because they screwed the ability to do direct date math up on the newer datatypes.
I'm also not sure what the big deal is here, either. Phil's code does the trick quite nicely.
BWAAA-HAAAA... and if you truly want to "go Celko", they're not "fields". 😀 😀 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2022 at 8:30 pm
I guess I'd have to disagree, Aaron
Doesn't happen often. As for storing dates and times separately it depends on what you want to do with them: If you are running lots of BETWEEN day dates then storing them separately makes for cleaner code (but again maintain a datetime field and use date and time as computed fields). I was suggesting storing the business key of RoomNumber as a single field and having computed columns for Floor number and roomLocator. You only need to maintain the FloorNumber but you have the component parts available if you need them.
September 2, 2022 at 1:04 am
Yes. That would work but how often would you actually have a need that couldn't be solve easily, like Phil did?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2022 at 10:20 am
Don't know. Not my system. The point I was making (and has obviously been lost in the message - my fault) was the distinction that in this particular domain Room Number is actually representing two distict pieces of information: the floor number and X/Y location of the room on the floor. There are lots of examples where one field actually represents several discreet pieces of data:
Postcode AB10 1AB = AG: Postal Town, 10: Inbound Postcode sector, 1: outbound route, AB: sub-route designation. It doesn't make sense to store these separately by default, but it may make sense to hold some or all of them separately as computed columns so that you don't have to do the splitting on-the-fly
Car Registration: DD22 ABC = DD: Registration office, 22: Registration half year, ABC: sequential registration identifier for uniqueness. Again data entry and reads would normally be done against the whole registration but it might make sense to store teh regisration office and registration year separately, especialy as the number plate format for the UK has been through a number of changes (e.g. older cars would have had FAC430Y: FA = registration office C430 = Unique identifier, Y = Registration year (or half year) ,
September 2, 2022 at 10:24 am
doesn't work if there are more than 9 floors, or the ground floor does not have leading zeros (which it won't if it is an INT) or if basement floors have negative room numbers, or if the needs to have multiple values. All of these are going to need to convert the int to a string and do some manipulation which is going to result in non-sargable queries.
September 2, 2022 at 10:58 am
doesn't work if there are more than 9 floors, or the ground floor does not have leading zeros (which it won't if it is an INT) or if basement floors have negative room numbers, or if the needs to have multiple values. All of these are going to need to convert the int to a string and do some manipulation which is going to result in non-sargable queries.
If I were designing it, I would use a string rather than an INT.
However, if the room numbers start at 100, there are fewer than 9 floors and each floor has room for only a handful of rooms, I see no issue whatsoever in using an INT in this case.
The physical constraints on the hotel mean that the points you have raised are very unlikely to be a factor, assuming the above is true.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 2, 2022 at 2:15 pm
Interesting debate. I agree that hotel room numbers are often not numerics, but strings. Numerics are used when we need some ordering, but if there wasn't a 505 on that floor for some reason, it wouldn't matter.
In most hotels, and I stay in a lot of them, the right(x, 2) or right(x, 3) is the room number and the left(y, 1) or left(y,2) is the floor. Don't know that many people would store those separately and use a computed column, but it would make sense. Easier than constantly breaking apart a string.
September 2, 2022 at 7:39 pm
aaron.reese wrote:Agreed, which is why it should be stored as two separate fields. Could do it on the table with a couple of computed columns.
Or do it the way I suggested.
For the specific case where you're excluding a single leading digit, SQL will have to scan most of the table/index anyway. It typically wont' really whether it's sargable or not for this specific case. Unless somehow floor 5 happens to have a largely disproportionate number of rooms in the hotel on that floor.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply