Web Development

Ask something
Question Locked

SQL/Access Database queries?

0 helpful
12 replies

by solshakdwn

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
What are some easy ways to run a query that selects only data that is of a certain CASE(eg all uppercase letters) without having to convert the existing data....The database I am quering is an Access DB and has data input in both Title case and uppercase and I just need to select the uppercase data....

12 replies

by solshakdwn

Thanks for your reply. The function works but only if I specify a full string eg TODAY... not all records are returned. I needed to be able to run the query and it provides all records from A-Z that are all uppercase. The ASC has the criteria structure that would work best for this but it only finds the first letter....This would not wor cause all the records are in Title case format....can I set something similar with the strcomp? I tried but I am not an advanced scriptor so I am sure I made syntax errors...

by cmiller5400

Try UCase(string)

That will change all of the characters to upper case.

by tony hopkinson

Select * From SomeTable Where StrComp(SomeField,Ucase(SomeField),0) = 0

The other thing to try is to see if in Access there is anywhere to set the collation

Latin General 1 is case sensitive, that's how you'd do this sort of thing in SQL server.

As a last ditch you could download sql server express (free from MS) and use a proper DBMS.

If you have and are using Acess as a development tool, you can link to SQL Server, get way more stuff same approcahble known front end.

HtHs

NB SQL server's default collation is case insensitive, but you can change that as the level of server, database, table, even column.

You still have problems say finding all text

TitleCase
camelCase

The only way quick/elegant way of doing that I can think of would be to use .net Extensions in SQL 2005/2008 and use regular expressions.

Whichever way you go about it, this is not scalable, it will get hideously slow, Personally I'd be looking at having another column called casetype and bulding it's content on insert and update.

HtHs

by solshakdwn

I just run the select query and it Gave exactly what I wanted. I thought that UCASE also commits the changes to the format but it just depends on how you construct the syntax...Is this correct?
Thanks also for the other suggestions I learnt something too.

by solshakdwn

That's my dilema, I don't want to change the format but select only those records that were inserted using UPPERCASE. For instance
AcBaCdDb > ABCD

by cmiller5400

Is AcBaCdDb one record? or is it multiple?

I notice that you are a "student", by chance is this homework?

by solshakdwn

Yes I am a student going for a degree in IT and no this is not homework....Its related to my work where am interning....so I don't have a title yet......
That was just an example and the AcBaCdDb are multiple records (record A, record b e.t.c)

by maevinn

So I'd exclude the records I don't want.

SELECT MyFieldName FROM tblName
WHERE (((StrComp(UCase([MyFieldName ]),[MyFieldName ],0))=-1));

Selects all the records that have LOWER Case. Run this, then find 'where not in'.

by solshakdwn

Thanks this also helped me validate my data and gave me another option on how I can represent my data. Helped me understand the process too.

by tony hopkinson

if you want to change then content then you'd use the function in say an Update statement.

This discussion is closed to new replies.