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 2005 Forums
 Transact-SQL (2005)
 parsing a field

Author  Topic 

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2010-12-03 : 09:43:38
I have the following with is a varchar field, here are a few of the records:


    Table Holding, Line 20562. Record could not be created. Portfolio_ID 9811L070007 does not exist
    Table Holding, Line 20581. Record could not be created. Portfolio_ID 9815D408009 does not exist
    Table Holding, Line 20583. Record could not be created. Portfolio_ID 9815L038005 does not exist
    Table Holding, Line 20587. Record could not be created. Portfolio_ID 9815T025004 does not exist
    Table Holding, Line 20597. Record could not be created. Portfolio_ID 98160073003 does not exist
    Table Holding, Line 20607. Record could not be created. Portfolio_ID 98190062000 does not exist
    Table Holding, Line 20608. Record could not be created. Portfolio_ID 9819D409009 does not exist
    Table Holding, Line 20609. Record could not be created. Portfolio_ID 9819L001005 does not exist
    Table Holding, Line 20611. Record could not be created. Portfolio_ID 9822D400002 does not exist


I would like to parse out the field so it reads


Table       Account       
Holdings    9822D400002
Holdings    9819L001005


and so one, any help with how to parse this would be great.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-03 : 09:56:14
something like

select 'Holdings',
substring(data,charindex('Portfolio_ID',data)+14,charindex(' ',data,charindex('Portfolio_ID',data)+14)-charindex('Portfolio_ID',data)-14)
from tbl


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2010-12-03 : 12:06:53
quote:
Originally posted by nigelrivett

something like

select 'Holdings',
substring(data,charindex('Portfolio_ID',data)+14,charindex(' ',data,charindex('Portfolio_ID',data)+14)-charindex('Portfolio_ID',data)-14)
from tbl


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.


ill give it a shot and let you know, thanks
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site