Q
Problem solve Get help with specific problems with your technologies, process and projects.

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


This was last published in April 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close