Maybe use looks_like_number(sv) prior to calling sv_2nv ...

#!/usr/bin/perl

use Inline C => <<'END_C';

void num_test(SV* sv) {

     I32 is_num = looks_like_number(sv);
     printf("'%s' looks like a number: %s  (SvNV(): %g)\n",
              SvPV_nolen(sv),
              is_num ? "yes":"no",
              SvNV(sv) ); 
}

END_C

num_test('aa');
num_test('123');
num_test('123aa');
num_test('aa123');
num_test('1.23');

__END__
'aa' looks like a number: no  (SvNV(): 0)
'123' looks like a number: yes  (SvNV(): 123)
'123aa' looks like a number: no  (SvNV(): 123)
'aa123' looks like a number: no  (SvNV(): 0)
'1.23' looks like a number: yes  (SvNV(): 1.23)
[download]

Update: added SvNV() results for comparison.

Thanks almut. This may be the way to go. ++ for the small example with Inline C too as I didn't realise you could do this so easily. The only bit I find confusing is why SvNV returns 0 for the non-number cases but I guess it can't do much else as it has to return an NV for some sorts.

The only bit I find confusing is why SvNV returns 0 for the non-number cases

Both sv_2nv/SvNV and sv_2iv/SvIV are coercing to the given type (and Perl does interpret "non-numeric" strings as zero...)

You may want to play with the following to get a better idea of what's going on for sv_2nv and sv_2iv (in particular, observe the flags NOK, IOK, and that both NV and IV are almost always being created):

#!/usr/bin/perl -w

use Inline C => <<'END_C';

void num_test(SV* sv) {

    sv_2nv(sv);
    //sv_2iv(sv);

    do_sv_dump(0, Perl_debug_log, sv, 0, 0, 0, 0);   // like Devel::Pe
+ek
}

END_C

num_test('aa');
num_test('123');
num_test('123aa');
num_test('aa123');
num_test('1.23');
[download]

Output for sv_2nv uncommented:

Argument "aa" isn't numeric in subroutine entry at ./809635.pl line 15
+.
SV = PVNV(0x669cf0) at 0x65ed30
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x665880 "aa"\0
  CUR = 2
  LEN = 8
SV = PVNV(0x7d8540) at 0x65ecb0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 123
  PV = 0x644a50 "123"\0
  CUR = 3
  LEN = 8
Argument "123aa" isn't numeric in subroutine entry at ./809635.pl line
+ 17.
SV = PVNV(0x7d85e0) at 0x65ecd0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 123
  PV = 0x644be0 "123aa"\0
  CUR = 5
  LEN = 8
Argument "aa123" isn't numeric in subroutine entry at ./809635.pl line
+ 18.
SV = PVNV(0x7d8608) at 0x65ed20
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x644cb0 "aa123"\0
  CUR = 5
  LEN = 8
SV = PVNV(0x840a58) at 0x65ed00
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pNOK,pPOK)
  IV = 0
  NV = 1.23
  PV = 0x665710 "1.23"\0
  CUR = 4
  LEN = 8
[download]

Output for sv_2iv uncommented:

Argument "aa" isn't numeric in subroutine entry at ./809635.pl line 15
+.
SV = PVNV(0x669cf0) at 0x65ed30
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x665880 "aa"\0
  CUR = 2
  LEN = 8
SV = PVIV(0x63e130) at 0x65ecb0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,POK,READONLY,pIOK,pPOK)
  IV = 123
  PV = 0x644a50 "123"\0
  CUR = 3
  LEN = 8
Argument "123aa" isn't numeric in subroutine entry at ./809635.pl line
+ 17.
SV = PVNV(0x7d8540) at 0x65ecd0
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 123
  NV = 123
  PV = 0x644be0 "123aa"\0
  CUR = 5
  LEN = 8
Argument "aa123" isn't numeric in subroutine entry at ./809635.pl line
+ 18.
SV = PVNV(0x7d85e0) at 0x65ed20
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,IOK,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 0
  NV = 0
  PV = 0x644cb0 "aa123"\0
  CUR = 5
  LEN = 8
SV = PVNV(0x7d8608) at 0x65ed00
  REFCNT = 1
  FLAGS = (PADBUSY,PADTMP,NOK,POK,READONLY,pIOK,pNOK,pPOK)
  IV = 1
  NV = 1.23
  PV = 0x665710 "1.23"\0
  CUR = 4
  LEN = 8
[download]

It's only by accident (kind of) that you get the desired result for SQL_INTEGER because you're doing cast_ok = (SvIOK(sv) && !SvNOK(sv)); and - as a special case - NV isn't being created for ints like 123.

why would sv_2nv convert "aa" to a [...] double

Because you asked it to.

how may I avoid this but still have it work for real numbers.

If you need an NV, "aa" would be an error. There are ways of checking that (looks_like_number), but you say you don't want that.

If you need an SV that contains a number if possible, you already have that. You don't need to do anything. Useless numifying is useless.

If it's not useless, it's because you have code that treats PV 1.2 different than NV 1.2, and that's a bug. Perl has an example of that (bitwise ops), and it has caused many headaches.

I don't mind an error if the SV does not contain anything that can be reasonably converted to a number so long as I can catch that. I want an NV when it is a convertable number and not otherwise.

Why?

If you needed an NV, you'd use

NV num;

if (!looks_like_number(sv))
    croak("Not a number");

num = SvNV(sv);
[download]

Put you said you didn't ("and not [an NV] otherwise"). If you simply needed a number when possible, you already have tat. Useless numifying is useless. If it's not useless, it's because you have code that treats PV 1.2 different than NV 1.2, and that's a bug. Perl has an example of that (bitwise ops), and it has caused many headaches.

Update: Added code.