Set variable and use in Select

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)

Set variable and use in Select

Author

Topic

Session101
Starting Member

15 Posts

Posted - 2004-09-08 : 16:16:39

I am extracting a string from a column
Example: ('attribs' = modifiedtime='9/8/2004';id=BB41000005)

I am trying to extract the toc_trade_id so I have this query:
declare @start int

select @start = patindex('%id=BB%', attribs)
from test
where pos = '444'

select id = substring(attribs, @start + 13, 11)
from test
where pos = '444'

I need to capture this id for each of the rows in my result set ( I have another select statement that is acquiring the id for each distinct row) . I am having trouble getting it as I cannot store a value into my variable and select data in the same SELECT statement. Is there an easy way around this?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-08 : 16:38:52

use subselects, and no variables. just keep returning values to the outer selects and pass them up the chain.


select 
  ID = Substring(attribs, Start+13,11)
from
(
  select Start = patindex('%id=BB%',attribs), attribs
  from test
  where Pos = '444'
) a

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources