Splitting a column into two separate columns

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

SQL Server 2008 Forums

Transact-SQL (2008)

Splitting a column into two separate columns

Author

Topic

spsubaseball
Starting Member

17 Posts

Posted - 2012-03-29 : 14:21:03

Hello, I'm having trouble doing this. I'm looking to split the following columns into two separate columns.

Test Data: Jeffrey A. Watkins
Glenn F Sherman
Campbell & Brannon, L.L.C.
Kelly E Waits
Martin G. Quirk
Robert W. Reardon
M. Todd Westfall
M. Todd Westfall
Robert W. Reardon
Jon R Erickson 950-40537
NEWTON & HOWELL, P.C.
Martin G. Quirk
McCurdy & Candler, L.L.C. 11-19069
Aldridge Connors, LLP 1165-311
S. H. McCalla 5533510
S. H. McCalla 51103111
Weissman, Nowack, Curry & Wilco, PC 010916
S. H. McCalla 5500111
S. H. McCalla 51543111
Martin & Brunavs 10-9411

I am trying to get the atty_name into one column and the file no into another column (ex: S.H. McCalla | 5533510)

Can someone help me please?

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-29 : 14:22:54

Is there always a space before the number?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

spsubaseball
Starting Member

17 Posts

Posted - 2012-03-29 : 14:24:45

Yeah there's always a space after the atty_name and file no. But not all have file no's I just want to split if they have a file number into another column.

Some file numbers have letters to:

L. J. Swertfeger BO-12-027329

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 14:53:13

sounds like this


SELECT CASE WHEN Field LIKE '%[0-9]%' THEN REVERSE(LEFT(REVERSE(field),CHARINDEX(' ',REVERSE(field))-1)) ELSE NULL END AS file_no,
CASE WHEN Field LIKE '%[0-9]%' THEN REVERSE(SUBSTRING(REVERSE(field),CHARINDEX(' ',REVERSE(field))+1,LEN(field)) ELSE Field END  AS atty_name
FROM table

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

spsubaseball
Starting Member

17 Posts

Posted - 2012-03-29 : 15:04:59

Viskah,

Thanks a lot that worked!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 15:28:12

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources