Substring in SQL 2008

How does one do a substring in SQL 2008 Management Studio.  I have a query that where I'd like to create a substring that takes an individuals organizational number and creates an absolute value to that I can determine Division, Department and Center for that individual.  

We've tried this with no luck:

,substr(spon.ORG_NUMBER, 1, 3)||'00' as [Sponsoring Mngr Ctr]

,substr(e.sl_deptname_abbrv, 1, 2)||'000' as Submitter_Div

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give us a before and after example of what you're trying to pull off here.
Aneesh RetnakaranDatabase AdministratorCommented:
here is the syntax
SUBSTRING ( expression ,start , length )

SELECT x = SUBSTRING('abcdef', 2, 3);
DallasTRAuthor Commented:
The above solution works to get the substring but now I want to and zeros to the end so that they look like this:  

Individuals Org Number: 12345
Group: 12340
Center: 12300
Division: 12000
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If this is a string..
Declare @str varchar(5) = '12345'
SELECT LEFT(@str, 4) + '0' as thegroup, LEFT(@str, 3) + '00' as center, LEFT(@str, 2) + '000' as division

Open in new window

If this is a number, and assuming no rounding..
Declare @str int = 12345
SELECT (@str / 10 * 10) as thegroup, @str / 100 * 100 as center, @str / 1000 * 1000 as division

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DallasTRAuthor Commented:
So in the end it ended up looking like this:

,pCreator.SL_NAME AS [Created By Name]
,SUBSTRING(pCreator.SL_DEPTNAME_ABBRV, 1,2) + '000' AS [Creator Division]
,SUBSTRING(pCreator.SL_DEPTNAME_ABBRV, 1,3) + '00' AS [Creator Center]
,SUBSTRING(pCreator.SL_DEPTNAME_ABBRV, 1,4) + '0' AS [Creator Group]
,pCreator.SL_DEPTNAME_ABBRV AS [Created By Org]

Creator Division      Creator Center      Creator Group      Created By Org
02000                      02500               02540               02548
04000                      04800               04870               04878
05000                      05900               05900               05900

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server Apps

From novice to tech pro — start learning today.