Extracting variable length data from text string

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)

Extracting variable length data from text string

Author

Topic

domo
Starting Member

23 Posts

Posted - 2008-04-09 : 05:33:13

I need to extract text from a string, starting at a certain position - then only reading up to the next space.

For example. I need to get the string XXXX123 from this record:

2008-04-09 10:12:23 XXXX123 Closed Changed

String XXXX123 can vary in length but the start position is always the same, so I will need to treat the space as a terminator.

Can someone help me with the T-SQL required to achieve this.

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 05:42:03

declare @s varchar(100)
set @s='2008-04-09 10:12:23 XXXX123 Closed Changed'
set @s=substring(@s,21,len(@s))
select substring(@s,1,charindex(' ',@s)-1)

Madhivanan

Failing to plan is Planning to fail

domo
Starting Member

23 Posts

Posted - 2008-04-09 : 05:50:36

quote:
Originally posted by madhivanan

set @s='2008-04-09 10:12:23 XXXX123 Closed Changed'

I am assuming I can replace '2008-04-09 10:12:23 XXXX123 Closed Changed' with the field name which contains this string?

Only this is part of a report, therefore that particular string will be different each time.

Thanks for your very rapid response madhivanan!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 06:04:19

To apply in a table, use

select substring(substring(col,21,len(col)),1,charindex(' ',substring(col,21,len(col)))-1)
from your_table

Madhivanan

Failing to plan is Planning to fail

domo
Starting Member

23 Posts

Posted - 2008-04-09 : 06:33:55

Thanks madhivanan - that works brilliantly!

domo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources