Separating out Invoice Numbers

[pearce64]

Newbie user question

I have Invoice Numbers I wish to separate out into 2 lists. Some Invoice Numbers are numeric only (list 1), the remaining are alpha numeric (list 2). The letters of the alphabet (A-Z) could be contained anywhere in the Invoice Number (either upper or lower case). Also Invoice numbers are of varying length in field size.

any help appreciated

526897Bg-172866
2062150T
7070580757747V
706998
1136473k
706995
7072671209353X
706952
573087
1137338N
0629647n
1017823P
7073600725218V
0281819B

 

[Golom]

Use the IsNumeric function as in
[blue][tt]
Select InvoiceNumber, f1, f2, f3, etc. ...
From tbl

WHERE Isnumeric ( InvoiceNumber )
[/tt][/blue]

or "NOT Isnumeric" for the other list.

 

[SVO4Turbo]

SELECT Test.Strings, IsNumeric([Strings]) AS StringsNum
FROM Test;

Returns:

Strings StringsNum
526897Bg-172866 0
2062150T 0
7070580757747V 0
706998 -1
1136473k 0
706995 -1
7072671209353X 0
706952 -1
573087 -1
1137338N 0
0629647n 0
1017823P 0
7073600725218V 0
0281819B 0


I think you can take it from here.