Question

Locked

Searching for Upper and Lower Case in Crystal Reports

By MissIT2007 ·
I have a SQL database of over 18,000 addresses and I have been asked to write two reports:
1)that highlights postcodes that have been typed in lowercase;
2)that highlights addresses that have been typed in uppercase.

I don't want to convert them at all, just find them and I'm writing the report in Crystal so that it runs automatically from our webserver and I never have to write it again.

Any suggestions would be much appreciated.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Depends,

by Tony Hopkinson In reply to Searching for Upper and L ...

if your database is using a case sensitive collation, (NOT the default) then

Select * From MyAddresses Where PostCode = Lower(PostCode)

or

Select * From MyAddresses Where PostCode = Upper(PostCode)

Will find all lower or uppercase

If it's not you have a few options
You could force it to one, but if it isn't now, you'll get issues all over the place.

You can be slightly sneaky

e.g.
Select * from MyAddresses
Where Convert(VarBinary,PostCode) = Convert(VarBinary(Lower(PostCode))

and of course
Select * from MyAddresses
Where Convert(VarBinary,PostCode) = Convert(VarBinary(Upper(PostCode))

Personally I'd choose to fix the UI to make uppercase standard and then just do an update

Update MyAddresses Set PostCode = Upper(PostCode)

to square up the old ones.

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums