Tricky Case Sensitive Query on SQL7

[Jeff]

I have a SQL7 database that was installed as case-insensitive.
/* Sort Order = 52, Case-insensitive dictionary sort order. */

This database contains a table that has a varchar column which contains
data such as:

'JUDICIARY; EDUCATION; Subcommittee on Justice and Judiciary'
'Subcommittee on Justice and Judiciary; TRANSPORTATION'
'Subcommittee on Cities; JUDICIARY; TRANSPORTATION'

I want to write a SELECT statement that gives me only those rows (1st
and 3rd) that have JUDICIARY (not Judiciary) in the varchar column.

This is SQL7 so I can't use COLLATE.

I tried

SELECT mycol
FROM mytable
WHERE mycol LIKE '%JUDICIARY%'
AND CAST(SUBSTRING(mycol ,PATINDEX('%JUDICIARY%',mycol
),LEN('JUDICIARY')) AS VARBINARY) = CAST('JUDICIARY' AS VARBINARY)

But this leaves out the row with JUDICIARY and Judiciary in it (only
returns 3rd row).

Any suggestions?

[Data]

If it is case SENSITIVE I would think a simple like '%JUDICIARY%'
would suffice.
Not having access to a case - sensitive database, I can't test.
kevin ruggles
"Jeff" <je**********@lrc.state.ky.us> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I have a SQL7 database that was installed as case-insensitive.
/* Sort Order = 52, Case-insensitive dictionary sort order. */

This database contains a table that has a varchar column which contains
data such as:

'JUDICIARY; EDUCATION; Subcommittee on Justice and Judiciary'
'Subcommittee on Justice and Judiciary; TRANSPORTATION'
'Subcommittee on Cities; JUDICIARY; TRANSPORTATION'

I want to write a SELECT statement that gives me only those rows (1st
and 3rd) that have JUDICIARY (not Judiciary) in the varchar column.

This is SQL7 so I can't use COLLATE.

I tried

SELECT mycol
FROM mytable
WHERE mycol LIKE '%JUDICIARY%'
AND CAST(SUBSTRING(mycol ,PATINDEX('%JUDICIARY%',mycol
),LEN('JUDICIARY')) AS VARBINARY) = CAST('JUDICIARY' AS VARBINARY)

But this leaves out the row with JUDICIARY and Judiciary in it (only
returns 3rd row).

Any suggestions?

[kevin ruggles]

I changed my display name.
kevin
"Data" <no**********@anywhereButHere.org> wrote in message
news:cp**********@gnus01.u.washington.edu...

If it is case SENSITIVE I would think a simple like '%JUDICIARY%'
would suffice.
Not having access to a case - sensitive database, I can't test.
kevin ruggles
"Jeff" <je**********@lrc.state.ky.us> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I have a SQL7 database that was installed as case-insensitive.
/* Sort Order = 52, Case-insensitive dictionary sort order. */

This database contains a table that has a varchar column which contains
data such as:

'JUDICIARY; EDUCATION; Subcommittee on Justice and Judiciary'
'Subcommittee on Justice and Judiciary; TRANSPORTATION'
'Subcommittee on Cities; JUDICIARY; TRANSPORTATION'

I want to write a SELECT statement that gives me only those rows (1st
and 3rd) that have JUDICIARY (not Judiciary) in the varchar column.

This is SQL7 so I can't use COLLATE.

I tried

SELECT mycol
FROM mytable
WHERE mycol LIKE '%JUDICIARY%'
AND CAST(SUBSTRING(mycol ,PATINDEX('%JUDICIARY%',mycol
),LEN('JUDICIARY')) AS VARBINARY) = CAST('JUDICIARY' AS VARBINARY)

But this leaves out the row with JUDICIARY and Judiciary in it (only
returns 3rd row).

Any suggestions?

[Erland Sommarskog]

[posted and mailed, please reply in news]

Jeff (je**********@lrc.state.ky.us) writes:

I have a SQL7 database that was installed as case-insensitive.
/* Sort Order = 52, Case-insensitive dictionary sort order. */

This database contains a table that has a varchar column which contains
data such as:

'JUDICIARY; EDUCATION; Subcommittee on Justice and Judiciary'
'Subcommittee on Justice and Judiciary; TRANSPORTATION'
'Subcommittee on Cities; JUDICIARY; TRANSPORTATION'

I want to write a SELECT statement that gives me only those rows (1st
and 3rd) that have JUDICIARY (not Judiciary) in the varchar column.

This appears to work:

CREATE TABLE jeff (a varchar(200) NOT NULL)
go
INSERT jeff (a) VALUES
('JUDICIARY; EDUCATION; Subcommittee on Justice and Judiciary')
INSERT jeff (a) VALUES
('Subcommittee on Justice and Judiciary; TRANSPORTATION')
INSERT jeff (a) VALUES
('Subcommittee on Cities; JUDICIARY; TRANSPORTATION')
go
declare @b varbinary(200)
select @b = convert(varbinary(200), 'JUDICIARY')
select * from jeff
where charindex(@b, convert(varbinary(200), a)) > 0
go
drop table jeff

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp