Select the first N digits of an integer

Question

I have a SQL Server table ("Activities") which has a column containing an ID, e.g. "10553100". This is the activity ID and is composed of two parts -> first 4 digits indicates which project it belongs to, and the last 4 digits indicates which sub project.

I need to extract the project number from the activityId column (aka the first 4 digits). Is it possible to do this using a simple SELECT statement in SQL?

Answer 1

Use the LEFT function.

SELECT activityId, LEFT(activityId, 4) AS projectnumber
FROM Activities

Or the SUBSTRING function.

SELECT activityId, SUBSTRING (activityId, 1, 4) AS projectnumber
FROM Activities

And if you want too include your subprojectnumber

LEFT and RIGHT functions.

SELECT activityId, LEFT(activityId, 4) AS projectnumber, RIGHT(activityId, 4) AS subprojectnumber
FROM Activities

SUBSTRING function.

SELECT activityId, SUBSTRING (activityId, 1, 4) AS projectnumber, SUBSTRING(activityId, 5, 4) AS subprojectnumber
FROM Activities

Answer 2

Try this

Select left(activityId,4) from Activities

Answer 3

If the field is numeric then:

SELECT Col / 10000 FROM TableName

If char the:

SELECT LEFT(Col, 4) FROM TableName

Answer 4

The left command yould work:

SELECT LEFT(id, 4) FROM dbo.Activities