Creation of 2 new columns from 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creation of 2 new columns from 1

Author

Topic

M66
Starting Member

1 Post

Posted - 2012-03-23 : 21:55:06

Hi,
Having a bit of an issue with determing the syntax for the following data extraction requirement.

I am required to create 2 new columns from 1 original where the data construct currently is as follows but where I want to exclude any spaces and the A/B at the end:

N2124535S B (want to transfer N2124535S into new column)
0294651317 A
Y00000019624N A

Reason is so that I will be able to match it with a different database table and attribute where the attribute does not contain spaces and A/B's.

Suggestions? Thank you in advance..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-23 : 22:42:23

How easy it is depends on how well-defined the data is. If it ALWAYS has a space followed by a single character at the end, you can do the following:

SELECT STUFF(orginalCol,LEN(orginalCol)-1,2,''),LEFT(REVERSE(orginalCol),1)

But, if it is not that well-defined, then you will need additional logic to handle the variety of cases that may exist in the data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-23 : 23:10:36

[code]
SELECT LEFT(field,CHARINDEX(' ',field)-1) from table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources