• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

Computed column by stripping leading text

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
NickHoward
Asked:
NickHoward
1 Solution
 
ddurgaprasadCommented:
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
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

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now