[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Computed column by stripping leading text

Posted on 2012-09-22
1
Medium Priority
?
414 Views
Last Modified: 2012-09-30
I have column name ORDER which has entries like:

ABC Order Number 2120
ABC Order Number 22
...

I want to have computed column ORDER2 where I want to have results from column ORDER as:

2120
22

In other words "ABC Order Number" need to strip which is always constant leading text.

Please advise what formula I create in my computed colum to achieve?

Thanks.

I am using SQL 2005 server.

Nick
0
Comment
Question by:NickHoward
1 Comment
 
LVL 2

Accepted Solution

by:
ddurgaprasad earned 960 total points
ID: 38424470
1) Use LTRIM(RTRIM(ORDER)) to remove any leading ,trailing spaces
2) LEN(LTRIM(RTRIM(ORDER)) ) provides you with string length
3) Constant leading text is always "ABC Order Number" ,whose length is 16 including 2 spaces
4) SELECT  SUBSTRING(LTRIM(RTRIM(ORDER)),17,LEN(LTRIM(RTRIM(ORDER)) )) AS ORDER2 should provide you trailing strings 2120 ,22 etc
5) You may want to converst them into Numeric values
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

831 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