Substring in SQL 2008

Posted on 2013-10-16
Medium Priority
Last Modified: 2013-10-16
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

Question by:DallasTR
  • 2
  • 2
LVL 66

Expert Comment

by:Jim Horn
ID: 39577581
Give us a before and after example of what you're trying to pull off here.
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39577583
here is the syntax
SUBSTRING ( expression ,start , length )

SELECT x = SUBSTRING('abcdef', 2, 3);


Author Comment

ID: 39577947
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
LVL 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 39577974
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


Author Comment

ID: 39578295
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


Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question