Data between Identifiers - SQL Server Forums

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)

Data between Identifiers

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-08-24 : 08:59:55

I have source file which is in the following format

id source
1 Person,Defendant,1961,a,M,John, Smith, Jr,F,Katie, Jones
2 Person,Attorney,n,M,John, Doe,M,Mark, Levey,
3 Person,Attorney,,y,F,samantha, Fox,M,Mathew, connell, IV,

I want the data between identfiers 'Person,' and ',M,', ',F,'.

for the above example output should be

id SOURCE1 SOURCE2 SOURCE3
______________________________________________________________________
1 Defendant,1961,a John, Smith, Jr Katie, Jones
2 Attorney,n John, Doe Mark, Levey,
3 Attorney,,y samantha, Fox Mathew, connell, IV,

Since the data is not fixed length between identifiers substring and charindex doesnt seem to work.

Any suggestions and inputs would help

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-24 : 09:09:34

You need to import the file to the staging table and then query

select replace(replace(replace(replace(source,' person,',' '),' and ',' '),',M,',' '),',F,',' ') as valid_source from staging_table

Madhivanan

Failing to plan is Planning to fail

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-08-24 : 09:44:04

Thanks madhivinan I changed the query little bit to add new delimiter instead of ',D,' and ',I,' so that I can parse it out

replace(replace(replace(replace(col004,' D,',' '),' and ',' '),',I,','||'),',D,','||') as valid_souce

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources