SQL to extract the Initial letter of a string? 1

[c0i0nes]

Is there any way to get the initial letter of a string value in a field using SQL?

For instance, I can say:

SELECT Master.Name, Asc([Master].[Name]) AS AscCode
FROM Master;

- which returns the Ascii code of the first letter of Name

Now all I need to do is return the Character corresponding to that AscCode value.

 

[mwolf00]

SELECT Master.Name, left([Master].[Name],1) as FirstInitial
FROM Master;
-----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[c0i0nes]

LEFT is a reserved word in Access SQL (LEFT JOIN &c.) The function is not available in expressions in queries. In Access 97 anyway.

 

[mwolf00]

It is available in Access 2000.

SELECT left(jobs.o_name,1)
FROM jobs;

What string functions are available to Access 97?

SELECT mid(jobs.o_name,1,1)
FROM jobs; -----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[c0i0nes]

I have just tried both Left() and Mid() as you suggested in a query in Access2002, and both return an "Undefined function" error message.

 

[mwolf00]

Can you paste your sql? Both of my last posts ran in access 2000 (I'm sure they would work in 2002....) -----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[c0i0nes]

Mike,
the SQL is:

SELECT PatientMaster.LastName, Mid(PatientMaster.LastName,1,1) AS init
FROM PatientMaster;

The table is called PatientMaster (just to be obvious) and whether bracketed [] or not, the same error message "Undefined function 'Mid' in expression." appears.

Any thoughts?

Chris

 

[mwolf00]

I have no clue. Can you run:

SELECT PatientMaster.LastName, date()
FROM PatientMaster;

I'm tryiong to see if you can call functions from your queries.... -----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[c0i0nes]

Mike I don't usually have problems running functions in queries e.g.:

SELECT PatientMaster.LastName, PatientMaster.DateOfBirth, Int((Now()-[dateofbirth])/365.25) AS age, Asc([Patientmaster].[lastname]) AS InitCode
FROM PatientMaster;

an excerpt of the output, (Lastnames truncated)

Last Name DateOfBirth age InitCode
Star* 01/03/1944 58 83
Stap***** 06/12/1942 60 83
Ston**** 83
Stua** 18/10/1935 67 83
Tayl** 27/08/1911 91 84

Works fine in my working Access97 database, and in Access2002

 

[mwolf00]

try the chr() function....

SELECT PatientMaster.LastName, PatientMaster.DateOfBirth, Int((Now()-[dateofbirth])/365.25) AS age, chr(Asc([Patientmaster].[lastname])) AS Init
FROM PatientMaster; -----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[c0i0nes]

I had tried this before also, and I get the same message now:
in Access2002

"Undefined function 'Chr' in expression.",

and in Access97

"Function isn't available in expressions in query expression 'Chr(Asc([PatientMaster].[LastName]))'."

Doh!!

 

[mwolf00]

You seem to be missing a librbary...

http://support.microsoft.com/default.aspx?scid=kb;en-us;275110

Make sure that you have JET 4.0

http://support.microsoft.com/default.aspx?scid=KB;en-us;q282010

-----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com

 

[c0i0nes]

Halfway there, thanks, Mike - 2002 now works as it should. Can I now get 97 to do the same? I'll let you know tomorrow - thanks again.

 

[c0i0nes]

Well, Mike after your help, for Access97, I managed to decompile, compact, upgrade to SR2b, which finally fixed the problem. So now I can get the initial character in a variety of ways:

SELECT PatientMaster.LastName, Chr(Asc([PatientMaster].[LastName])) AS Code, Left([LASTNAME],1) AS alt, Mid([LASTNAME],1,1) AS ALT2
FROM PatientMaster;


Last Name Code alt ALT2
Tyle* T T T
Tyrr*** T T T
Tyso** T T T
Unde*** U U U
Vagh** V V V
Vahe** V V V

I wonder which is most efficient :-D

 

[mwolf00]

Awesome - I'd guess you WERE missing the library..... -----------------------------------------------------------------
Be nice. It's only doing what you tell it to do.
mikewolf@tst-us.com