-- prepare test data
declare @test table (rowid int identity,data varchar(100))

insert	@test
select	'123 waste' union all
select	'waste 567' union all
select	'waste' union all
select	'789' union all
select	'101 202 vaste vdfg' union all
select	'waste 303 erwer'

-- do the dirty work
declare @stage table (rowid int, i int, part varchar(100), main int)

declare @row int,
	@maxrow int,
	@data varchar(100)

select	@row = min(rowid),
	@maxrow = max(rowid)
from	@test

while @row <= @maxrow
	begin
		select	@data = data
		from	@test
		where	rowid = @row

		insert	@stage
		select	@row, i, part, case when part in ('waste', 'vaste') then i else null end
		from	dbo.fnSplitDelimitedString(@data, ' ')

		select	@row = min(rowid)
		from	@test
		where	rowid > @row
	end

-- present the data
select		s.rowid,
		s.part
from		@stage s
inner join	(
			select	rowid,
				main
			from	@stage
			where	main is not null
		) z on z.rowid = s.rowid
where		s.i in (z.main - 1, z.main + 1)
		and patindex('%[^0-9]%', s.part) < 1

quote:

---

could you post your "fnSplitDelimitedString" function for me please so I can run it and have a play around?

---

CREATE FUNCTION dbo.fnSplitDelimitedString 
( 
        @Text VARCHAR(8000), 
        @Delimiter VARCHAR(8000) 
) 
RETURNS @Parts TABLE 
        ( 
                i SMALLINT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, 
                Part VARCHAR(8000) 
        ) 
AS 

BEGIN 
        DECLARE        @LastIndex SMALLINT, 
                @NextIndex SMALLINT 

        IF @Text IS NULL OR DATALENGTH(@Text) = 0 
                RETURN 

        IF @Delimiter IS NULL 
                SELECT        @Delimiter = ';' 

        SELECT        @LastIndex = 0, 
                @NextIndex = 1 

        WHILE @NextIndex > 0 
                BEGIN 
                        SELECT        @NextIndex = CHARINDEX(@Delimiter, @Text, @LastIndex + 1) 

                        INSERT        @Parts 
                                ( 
                                        Part 
                                ) 
                        SELECT        CASE 
                                        WHEN @NextIndex = 0 THEN SUBSTRING(@Text, @LastIndex + 1, DATALENGTH(@Text) - @LastIndex) 
                                        ELSE SUBSTRING(@Text, @LastIndex + 1, @NextIndex - @LastIndex - 1) 
                                END 

                        SELECT        @LastIndex = @NextIndex 
                END 

        RETURN 
END

CREATE FUNCTION dbo.fnSplitFixedString 
( 
        @Text VARCHAR(8000), 
        @PartLength SMALLINT, 
        @WantedStart SMALLINT, 
        @WantedLength SMALLINT 
) 
RETURNS @Parts TABLE 
        ( 
                i SMALLINT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, 
                Part VARCHAR(8000) 
        ) 
AS 

BEGIN 
        DECLARE        @Index SMALLINT 

        IF @PartLength < 1 OR @PartLength IS NULL 
                SELECT @PartLength = 1 

        IF @PartLength > DATALENGTH(@Text) 
                SELECT @PartLength = DATALENGTH(@Text) 

        IF @WantedStart < 1 OR @WantedStart IS NULL 
                SELECT @WantedStart = 1 

        IF @WantedStart > @PartLength 
                SELECT @WantedStart = @PartLength 

        IF @WantedLength < 1 OR @WantedLength IS NULL 
                SELECT @WantedLength = 1 

        IF @WantedLength > @PartLength - @WantedStart + 1 
                SELECT @WantedLength = @PartLength - @WantedStart + 1 

        SELECT        @Index = 0 

        WHILE @Index < DATALENGTH(@Text) / @PartLength 
                BEGIN 
                        INSERT        @Parts 
                                ( 
                                        Part 
                                ) 
                        SELECT        SUBSTRING(@Text, @WantedStart + @Index * @PartLength, @WantedLength) 

                        SELECT        @Index = @Index + 1 
                END 

        RETURN 
END