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('%JUD ICIARY%',mycol
),LEN('JUDICIAR Y')) 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]

Re: Tricky Case Sensitive Query on SQL7

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" <jeff.strange@l rc.state.ky.us> wrote in message
news:1103209247 .709113.255750@ f14g2000cwb.goo glegroups.com.. .[color=blue]
>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('%JUD ICIARY%',mycol
> ),LEN('JUDICIAR Y')) AS VARBINARY) = CAST('JUDICIARY ' AS VARBINARY)
>
> But this leaves out the row with JUDICIARY and Judiciary in it (only
> returns 3rd row).
>
> Any suggestions?
>[/color]

[kevin ruggles]

Re: Tricky Case Sensitive Query on SQL7

I changed my display name.
kevin
"Data" <nospamthanks@a nywhereButHere. org> wrote in message
news:cpsf3f$67o $1@gnus01.u.was hington.edu...[color=blue]
> 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" <jeff.strange@l rc.state.ky.us> wrote in message
> news:1103209247 .709113.255750@ f14g2000cwb.goo glegroups.com.. .[color=green]
>>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('%JUD ICIARY%',mycol
>> ),LEN('JUDICIAR Y')) AS VARBINARY) = CAST('JUDICIARY ' AS VARBINARY)
>>
>> But this leaves out the row with JUDICIARY and Judiciary in it (only
>> returns 3rd row).
>>
>> Any suggestions?
>>[/color]
>
>[/color]

[Erland Sommarskog]

Re: Tricky Case Sensitive Query on SQL7

[posted and mailed, please reply in news]

Jeff (jeff.strange@l rc.state.ky.us) writes:[color=blue]
> 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.
>[/color]

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(varbina ry(200), 'JUDICIARY')
select * from jeff
where charindex(@b, convert(varbina ry(200), a)) > 0
go
drop table jeff

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp