Split Values - 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 2000 Forums

Transact-SQL (2000)

Split Values

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-20 : 01:04:42

Dear All,
I need to get the Ref.Number from The following lines

in first line i need FF074
2 nd line OE002
3 rd line OE002
4 th line OE002
... like that
The length of the rearks sholud be differ..

Acquisition of FF074 on 01 Jan 2004
Transfer of cumulative depreciation ofOE002-Sold on credit
Transfer of cumulative depreciation ofOE002-Sold on credit
Retirement of Asset IdOE002-Sold on credit
Retirement of Asset IdOE002-Sold on credit
Loss on retirement ofOE002
Loss on retirement ofOE002
Acquisition of OE020 on 26 Feb 2004

Is it possible to get the perticular value..?

Thanks
Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-20 : 04:30:03

[code]declare @test table (txt varchar(8000))

insert @test
select 'Acquisition of FF074 on 01 Jan 2004' union all
select 'Transfer of cumulative depreciation ofOE002-Sold on credit' union all
select 'Transfer of cumulative depreciation ofOE002-Sold on credit' union all
select 'Retirement of Asset IdOE002-Sold on credit' union all
select 'Retirement of Asset IdOE002-Sold on credit' union all
select 'Loss on retirement ofOE002' union all
select 'Loss on retirement ofOE002' union all
select 'Acquisition of OE020 on 26 Feb 2004'

select substring(txt, patindex('%[a-z][a-z][0-9][0-9][0-9]%', txt), 5) from @test[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources