Instr to find the position of the first Alpha character?

[boostbyslinky]

Folks, any thoughts on this one?

I need to find the position of the first alpha character in a string, the section of the string prior to the alpha character is variable in length, hence I need to be able to find the position to feed to a left/right/mid style expression to be able to break down the original string into useable parts.

I need to do this across circa 440k rows, so an "in query" solution would probably be best..

TIA,

slinky

 

[John Big Booty]

You could use the Val() function to identify the leading Numeric characters and then work backwards from there.

 

[boostbyslinky]

That's where I'm heading at the moment, but I'm getting some slightly peculiar results on some values..

Query8

StoreRef Aisle AisleLen
13C45 13 2
13C46 13 2
13D11 1300000000000 13
13D12 13000000000000 14

query is as follows..

SELECT [2012-08-20].StoreRef, Val([StoreRef]) AS Aisle, Len(Val([StoreRef])) AS AisleLen
FROM [2012-08-20]
GROUP BY [2012-08-20].StoreRef;

Any thoughts on how I can get around this?

 

[stopher]

Looks like D and E are being treated as exponents. So convert them to another letter first. So Aisle becomes

Left([storeref],LEN(Val(Replace(Replace([storedref],'D','Z'),'E','Z'))))

 

[boostbyslinky]

Perfect, thanks both..