SQL Server Query

Question

I'm trying to do some work with my SQL table.

I have 2 buildings with room numbers 1 - 100 in building 1 and 101 - 199 in building 2. I have a location field (which I've just created) and want to run a query to populate it with either 'Building 1' or 'Building 2' depending on which room number it has in the 'Room' field.

Many thanks for your help.

Regards

Scott

@Galwegian: Can't understand why would you delete your post, if the answer is correct? — Kamran Khan
– Kamran Khan, Commented May 5, 2010 at 10:29
Galwegian posted an anwer that was correct, but has since deleted it. — Neil Knight
– Neil Knight, Commented May 5, 2010 at 10:38

Peter Lang · Accepted Answer · 2010-05-05 11:24:44Z

4

You can use Case to update your table:

Update your_table
Set location = Case When room_number <= 100 Then
                       'Building 1'
                    Else
                       'Building 2'
                    End;

If all of your room-numbers have three digits followed by some other characters, using substring should work:

Case When Cast( Substring( room_number, 1, 3 ) As Int ) <= 100 Then

Since not all of your rooms start with three digits (1A) you can use PatIndex to find the length of the number:

Case When
   Cast(
      Substring( room_number, 1, PatIndex( '%[A-Z]%', room_number || 'A' ) - 1 )
   As Int ) <= 100
Then

I'm concatenating the A to make sure it also works with numerical room-numbers.

edited May 5, 2010 at 11:24

answered May 5, 2010 at 10:20

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Scott Jackson Over a year ago

Thanks. How would I get it to cope with room numbers such as 120Z and 121Y ?

Neil Knight Over a year ago

If all of your room numbers are 3 digits and a single character, you could use a SUBSTRING on the room number and compare that value in the case statement.

Scott Jackson Over a year ago

My room numbers go from 1 to 500 and have others inbetween such as 120X, 121G, etc (only about 20 rooms though).

Scott Jackson Over a year ago

Would it make it a real pain if my room field was nvarchar ? (which it is)

Scott Jackson Over a year ago

Thanks. When I run it I get an error.... 'Syntax error converting the nvarchar value '1A' to a column of data type int'

|

Collectives™ on Stack Overflow

SQL Server Query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related