Select everything but the last three numbers in the field query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Moe - 30 May 2006 19:59 GMT

I have a query that select the first 14 chars from the field. The field
data is something like the following
PGLV120060215-AT001
DLLCV220060310-AR002
TPKGMV120060415-AT001
and so on...

I want to only select everything before the last three numbers such as

PGLV120060215-AT
DLLCV220060310-AR
TPKGMV120060415-AT
and so on ....
Note: the last three numbers will remain like 001,002,003, and so on
... for each record and i dont want to select them i just want to
select everything that is before them.
I have the following query that slects only first 14 chars from the
field but it may vary to 12 or 13 or 10 or anything..

SELECT DISTINCT Left([FINDG_NO],14) AS Expr1 FROM tblTest1;

Can somone help me out with this please

Moe

Reply to this Message

KARL DEWEY - 30 May 2006 20:52 GMT

Try this --
SELECT DISTINCT Left([FINDG_NO],Len([FINDG_NO])-3) AS Expr1 FROM tblTest1;

> I have a query that select the first 14 chars from the field. The field
> data is something like the following
[quoted text clipped - 20 lines]
>
> Moe

Reply to this Message

Moe - 31 May 2006 15:32 GMT

Thanks it worked perfectly.

> Try this --
> SELECT DISTINCT Left([FINDG_NO],Len([FINDG_NO])-3) AS Expr1 FROM tblTest1;
[quoted text clipped - 23 lines]
> >
> > Moe

Reply to this Message