I have char (1000) field in my table

It contains something like binary numbers i.e.
00111000100001000000111110000...

I need to take substring (calculating start and length parameters based on
some other fields and parameters) of that field and find out if there is any
'1' or all are zeroes

That should be in where clause, basically I need to find only rows that have
all zeroes in substring

Ok...perhaps not the best approach for performance but I believe this may
get you started...

CREATE TABLE BIN
(Col1 char(1000) NOT NULL)
go

insert bin
values ('00111000100001000000111110000')
go
insert bin
values ('00111100100001000000111110000')
go

select *
from bin
where substring(col1,6,3) NOT LIKE '%1%'


Show quoteHide quote

You can try:
Where convert(int, Substring(yourfield, .. )) = 0

Perayu

Show quoteHide quote

Try,

declare @i int
declare @l int

set @i = 6
set @l = 3

select c1
from t1
where substring(c1, @i, @l) not like '%[^0]%'


Sql server will not perform an index seek if there is an index by [c1].


AMB

Show quoteHide quote

Show quote Hide quote Thanks,



I am not sure if I can put index on char (1000) field

What is faster not like '%1%' or convert(int, Substring(yourfield... )) = 0



Tomislav

The question of which approach is faster is best answered with a test of
both approaches.  The Display Estimated Execution Plan can be used without
actually running the query if a large time is involved.

HTH

Jerry
Show quoteHide quote

On Fri, 16 Sep 2005 08:30:59 +0200, TomislaW wrote:

Hi Tomislav,

You can't. The length of all columns in an index is limited to 900
bytes.

If the column is varchar(1000) instead of char(1000), you can create the
index, but you'll get an error whenever you try to insert data that's
over 900 characters long.

Did you test both versions? Here's how:

-- Make sure that you start with clean cache
CHECKPOINT
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
go
DECLARE @start datetime, @end datetime
DECLARE @i int, @l int
SET @i = 3    -- Modify as needed
SET @l = 6    -- Modify as needed
SET @start = CURRENT_TIMESTAMP
SELECT c1
INTO   #tmp1    -- This eliminates network/formatting overhead
        -- from the speed measurement
FROM   t1
WHERE  SUBSTRING(c1, @i, @l) NOT LIKE '%1%'
--WHERE  CONVERT(int, SUBSTRING(c1, @i, @l)) = 0
SET @end = CURRENT_TIMESTAMP
SELECT @start AS StartTime,
       @end AS EndTime,
       DATEDIFF(ms, @start, @end) AS MillisecondsTaken
-- optionally show results, for correctness check
-- SELECT * FROM #tmp1
-- clean up
DROP TABLE #tmp1
go


Best, Hugo

Selecting when Dates in where clause
"If you want a lot of information, one has to use cursors."
How to get variable number of rows on one row.
Returning a value from query
slow Select query specifying daterange using variables.