OK so given that we do have a solid way to order the table. There still isn't really any way to just get a pattern of any combination of values....

Given that the files are converted to byte array they are no longer human readable. There is no way you will be able to search the contents of these files...

Are you only looking A - B - A patterns? Otherwise the distinction of it being a pattern is pretty vague. For example if you have a few million rows...

Is this what you are looking for?

create table #test (id int, batch varchar(5))

insert into #test values (1,'abc')

insert into #test values (1,'abc')

insert into #test values (2,'abc')

insert into #test values (2,'abc')

insert into...

Welcome to SSC. Notice how i posted some sample ddl and data? Check out the link in my signature for best practices on posting questions.

You can achieve this doing something...

sql2k8 (7/18/2011)

I have below TableA in database under SQL SERVER 2008 R2

CREATE TABLE [dbo].[TableA](

[Server] [nvarchar](256) NULL,

[DatabaseName] [nvarchar](256) NULL,

[BackupStartDate] [varchar](20) NULL,

[BackupFinishDate] [varchar](20) NULL,

[File_Size] [float] NULL,

[Time] [numeric](10, 2) NULL,

[Physical_Name] [varchar](500)...

Jeff Moden's article here[/url]. It was suggested above, a suggest it again. Read this, it is exactly what you are trying to do.

Or you could just reverse your logic in this case.

select * from test where name1 like '%' + @string '%'

This may or may not be slower than splitting depending on...

Actually reading further i think this might be closer...

select DrugID,InventoryID

from DPhaDrugInventories a

join DPhaDrugInventories b on a.DrugID = b.DrugID

where b.InventoryID like 'PX-4%'

or b.InventoryID like 'PX-5%'

or InventoryID in ('FLR-4N','FLR-5N','FLR-6N','FLR-7N','FLR-8N')

order by DrugID

something like this?

select DrugID,InventoryID

from DPhaDrugInventories a

join DPhaDrugInventories b on a.DrugID = b.DrugID

where

(

b.InventoryID = 'FLR-4N'

or b.InventoryID like 'PX-4%'

or b.InventoryID = 'FLR-5N'

or b.InventoryID like 'PX-5%'

)

and InventoryID in ('FLR-4N','FLR-5N','FLR-6N','FLR-7N','FLR-8N')

order by DrugID

This is possible but the only way I know of would be hideously slow. I assume you have column varbinary(max) and in this column you have stored the byte []...

Of course I understand sensitive information. The real point is that if you can send some ddl and sample data for your example I can spend my time working on...

Can you post some ddl, sample data and desire results based on your sample data? Check out the link in my signature for best practices on posting ddl and data.

This looks a lot like homework. What have you tried so far?

Jeff Moden (7/18/2011)

It's just me, I know, but I name my tables after what a single row of any given table contains. Part of the...