Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

## kaz1963

2 posts

Wannabe Geek

Topic # 60231 22-Apr-2010 14:21

Hi there

I need to count the number of records in a table where there are more than three alpha characters in a specific field.

So if there were two records :

Record 1 : Field=OT12/12BV
Record 2 : Field=OT12/12B

and I ran the query, the count would be 1 - as the first record has four alpha characters in the field and the second record has three alpha characters in the field.

I have searched for other questions like this but I guess it is an unusual one as I can't find anyone trying to do exactly this.

Note: the count is for characters A - Z - not special characters.

Any help would be appreciated.

## rvangelder

352 posts

Ultimate Geek

You're going to hate this:

select * from MyTable where
Len(MyField) - Len(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(MyField, "A", ""), "B", ""), "C", ""), "D", ""), "E", ""), "F", ""), "G", ""), "H", ""), "I", ""), "J", ""), "K", ""), "L", ""), "M", ""), "N", ""), "O", ""), "P", ""), "Q", ""), "R", ""), "S", ""), "T", ""), "U", ""), "V", ""), "W", ""), "X", ""), "Y", ""), "Z", "")) > 3

I hate it too. Oracle SQL has a translate function that makes this quite a lot shorter.

## kaz1963

2 posts

Wannabe Geek

lol - yeah that really is ugly huh? Will give it a go.

## rvangelder

352 posts

Ultimate Geek

another way is to cartesian join the table with a number table (single column table, where each row is a number in sequence, 1 to 1000), then use an ASC function on MID to return 1 if the ascii code is between 65 and [whatever code for Z] or 0 if it's not A-Z, then sum the result.
it would be quite a lot shorter, but not as easy to understand, from a maintenance standpoint.

News »

Opera launches new mobile browser: Opera Touch
Posted 25-Apr-2018 20:45

TCF and Telcos Toughen Up on Scam Callers
Posted 23-Apr-2018 09:39

Amazon launches the International Shopping Experience in the Amazon Shopping App
Posted 19-Apr-2018 08:38

Spark New Zealand and TVNZ to bring coverage of Rugby World Cup 2019
Posted 16-Apr-2018 06:55

How Google can seize Microsoft Office crown
Posted 14-Apr-2018 11:08

How back office transformation drives IRD efficiency
Posted 12-Apr-2018 21:15

iPod laws in a smartphone world: will we ever get copyright right?
Posted 12-Apr-2018 21:13

Posted 9-Apr-2018 12:11

111 mobile caller location extended to iOS
Posted 6-Apr-2018 13:50

Huawei announces the HUAWEI P20 series
Posted 29-Mar-2018 11:41

Symantec Internet Security Threat Report shows increased endpoint technology risks
Posted 26-Mar-2018 18:29

Spark switches on long-range IoT network across New Zealand
Posted 26-Mar-2018 18:22

Stuff Pix enters streaming video market
Posted 21-Mar-2018 09:18

Windows no longer Microsoftâ€™s main focus
Posted 13-Mar-2018 07:47

Why phone makers are obsessed with cameras
Posted 11-Mar-2018 12:25

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.