Hi all
I would like to know in a select if my varchar field contains only
alphabetic caracter '
Is there a Function that can tell me this '
Thank
NicWell..you can use the IsNumeric() function like so
Select F1...Fn
From TableName
Where IsNumeric(F1) = 0
However, IsNumeric can sometimes return funky results. For example,
IsNumeric('.') returns 1.
Another way would be:
Select F1...Fn
From TableName
Where F1 Like '[^0-9]'
This expression finds results that do not contain the characters 0...9. The
downside to this approach is that a value of '02A' for example would also be
excluded even though it is not a number per se.
HTH
Thomas|||You could write one...
Create FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
RETURNS TinyInt
AS
BEGIN
Declare @.HasNonAlpha TinyInt
Declare @.A TinyInt
While Len(@.Value) > 0 Begin
Set @.A = Ascii(@.Value)
Set @.Value = Substring(@.Value, 2, Len(@.Value))
If @.A Not Between Ascii('a') And Ascii('z')
And @.A Not Between Ascii('A') And Ascii('Z')
And @.A <> Ascii(' ') Return 0
End
Return 1
RETURN @.HasNonAlpha
END
"Nicolas Veilleux" wrote:
> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
>|||Refactored...
CREATE FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
RETURNS TinyInt
AS
BEGIN
Declare @.A TinyInt
While Len(@.Value) > 0 Begin
Set @.A = Ascii(@.Value)
Set @.Value = Substring(@.Value, 2, Len(@.Value))
If @.A Not Between Ascii('a') And Ascii('z')
And @.A Not Between Ascii('A') And Ascii('Z')
And @.A <> Ascii(' ') Return 0
End
Return 1
End
"CBretana" wrote:
> You could write one...
> Create FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
> RETURNS TinyInt
> AS
> BEGIN
> Declare @.HasNonAlpha TinyInt
> Declare @.A TinyInt
> While Len(@.Value) > 0 Begin
> Set @.A = Ascii(@.Value)
> Set @.Value = Substring(@.Value, 2, Len(@.Value))
> If @.A Not Between Ascii('a') And Ascii('z')
> And @.A Not Between Ascii('A') And Ascii('Z')
> And @.A <> Ascii(' ') Return 0
> End
> Return 1
>
> RETURN @.HasNonAlpha
> END
> "Nicolas Veilleux" wrote:
>|||"Nicolas Veilleux" <nveilleux@.nbautomation.com> wrote in message
news:u9$bVx3RFHA.3120@.TK2MSFTNGP10.phx.gbl...
> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
SELECT
CASE PATINDEX('%[^a-z ]%','This string contains the number 1')
WHEN 0 THEN 'Alphabetic'
ELSE 'Non-alphabetic'
END|||How about :
SELECT PATINDEX('%[^A-Z]%', 'ab1c'), PATINDEX('%[^A-Z]%', 'abc')
If PATINDEX returns 0 then the string contains a non-alpha character.
Might need to be modified for case-sensitive collations.
- KH
"Nicolas Veilleux" wrote:
> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
>|||Sorry - if PATINDEX returns *NON-ZERO* then the string contains a non-alpha
character.
"KH" wrote:
> How about :
> SELECT PATINDEX('%[^A-Z]%', 'ab1c'), PATINDEX('%[^A-Z]%', 'abc')
> If PATINDEX returns 0 then the string contains a non-alpha character.
> Might need to be modified for case-sensitive collations.
> - KH
>
> "Nicolas Veilleux" wrote:
>|||First of all, columns are not fields; learn to think correctly.
CHECK
( LEN
(REPLACE (
REPLACE (
. REPLACE (UPPER(foobar), 'A', '')
.
'Z', '') = LEN(foobar))
Basically, uppercase the string, use nested REPLACE() functions to turn
the alphas into striings and check the length. Do not create a
function with a WHILE loop or other procedural stuff in SQL. That
misses the whole point of a declarative language.
SQL Server has a PATINDEX() which can also be used; I am not sure which
is faster. The REPLACE () is a scan in main storage, but PATINDEX()
has to build a finite state machine.
