Changing a 4-digit number into 3 digits

In our database where we maintain building, floor and room number information for all our locations worldwide, I need to remove the first digit from each room number to change from a 4-digit room number to a 3-digit room number, i.e., 1234 becomes 234. What would be the correct SQL statement to do this? The table name is RM and the field name RM_ID.

If the room number is a character datatype, you just need the substring starting at the second position --

SELECT SUBSTRING(RM_ID FROM 2 FOR LENGTH(RM_ID)-1)

This is SQL-92 syntax; you may need to check your database for the appropriate syntax. If the FOR value is omitted, you get the substring that goes right to the end of the original string. So, for example, in SQL/Server you could have

SELECT SUBSTRING(RM_ID, 2)

If the room number is a numeric datatype, first convert it to a string and then take the substring. Note that you want the string to be positioned left, so make sure to trim any leading blanks --

SELECT SUBSTRING(
          LTRIM(CAST(RM_ID as VARCHAR(16)))
          FROM 2)

For More Information

• What do you think about this answer? E-mail us at [email protected] with your feedback.
• The Best SQL Web Links
• Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our SQL guru is waiting to answer your toughest questions.