I am building a small database that is somewhat like a ledger for pricing team here. In a given month the get 15 cases or so. I made the case number my pk. A case number is the pricer's first name and last name then a three digit number. So RB001. Then I have a table that has initials and full names. What I want to do is to be able to rip the RB out and compare that against the table with the names, so the pricers can pull records by their name...

Try: Don't know if this is standard SQL but it works fine in Informix...

Regards

SUBSTRING(casenumber FROM 1 FOR 2)

also, it will probably help if you would post your questions in the forum specific to your particular database system

this forum is for standard SQL, the language, and while all database systems support standard SQL to one degree or another, it is in the area of functions that support is most sporadic (to say nothing of the availability of many non-standard but quite useful functions in different databases)

Or simply: SUBSTRING(casenumber, 1, 2)

In M$ Access you may need to use: LEFT(casenumber,1)

unless Access automatically doubles up bytes, i would suggest LEFT(casenumber,2)

Ooops, yes that was a typo.

Yet some other systems will need

An interesting alternative (especially in terms of performance) could be to define a two-column PK. The first column would then have "RB" and the second one "001", which (1) avoids the costly substring() construct, and (2) allows e.g. having an index on that first column for efficient retrieval. Moreover it's more flexible in that it will easily allow e.g. 3-letter initials in the future without having to change any of your queries (which is not the case now).

It is an Access DB. But seeing this was strictly how to pull information from a table USING SQL, I figured it was more appropriate to ask a question directly to the SQL Forum.

Actually it'd be left$(CaseNo,2), but this doesn't work.

Peter - I think I may have to split them up like you said.

there is sql, and there is sql, and there is standard sql...

... and then there is access sql

the whole point about what we are telling you is that unless you know the difference between the various types of sql, and how they are likely to vary from standard sql, it would be far better for you if you would post in the specific forum for your specific database

just trying to save you the agro, man

ok thanks, i'll see what i can do