Parse a column - 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)

Parse a column

Author

Topic

bwreath
Starting Member

2 Posts

Posted - 2004-03-25 : 10:29:01

Hi,

I would appreciate help on the following.
I have a column in a table. Each entry in the column can
have 0,1,2,3... maybe four entries in it.

I would like to parse the column and grab each of the entries and
feed it into another select statement. This entry that needs
to be parsed would be found through a select statement.
I would like to know what a good way is for doing this.

For example an entry in the column may be
AI732541 AI791498 AA508861
another entry may be
AA508867

Basically, in the first example
AI732541 AI791498 AA508861 would be the the answer ie. access_num for my first
select statement
Select access_num from inventory where prod_id = current_prod_id;

I would like to parse the entry
to allow me to access AI732541 to feed into another select statement
find out the answer and capture in a variable.
Then I would like to be able to access the next entry AI791498 and
perform the same task of feeding it into another select statement
and capturing the answer in a variable an so on.

thanks in advance,
bwreath

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-03-25 : 10:37:12

Are you looking something like this ??

Select * from tablea where cola in (Select Replace ('AI732541 AI791498 AA508861', ' ', ','))

- Sekar

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-25 : 10:45:40

Or more generically, have a loop process that does seomthing like this:

SELECT @Varname = columnname FROM tablename WHERE <criteria>
WHILE CHARINDEX(' ', @Varname) > 0
BEGIN
<your processing here on the LEFT(@Varname, CHARINDEX(' ', @Varname))>

SET @Varname = SUBSTRING(@Varname, CHARINDEX(' ', @Varname))

IF CHARINDEX(' ', @Varname) = 0
-- process the last string here
END

Sorry to be so vague, but you didn't post any DDL for us.

Raymond

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources