By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

Tricky Case Sensitive Query on SQL7

P: n/a
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?

Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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?

Jul 23 '05 #2

P: n/a
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?


Jul 23 '05 #3

P: n/a
[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
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.