select only Numeric values from AlphaNumeric column? 2

[Kliot]

I have a table that has a alphanumeric column, some entries are alphnumeric others are numeric. How can I do a query that only returns the rows with numeric entries?

Thanks

 

[gmmastros]

Any numeric...
[tt][blue]
Select *
From Table
Where IsNumeric(ColumnName) = 1
[/blue][/tt]



Exclude scientific notation...
[tt][blue]
Select *
From Table
Where IsNumeric(ColumnName + 'e0') = 1
[/blue][/tt]



Integer values only...
[tt][blue]
Select *
From Table
Where IsNumeric(ColumnName + '.0e0') = 1
[/blue][/tt]


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[genomon]

Some values of "F" and "H" return valid numerics as well.
This user-defined fn has worked well for us:

CREATE function fn_numeric
-- input is varchar string.
   (@string varchar(12) )
returns bit -- 0 = false, 1 = true
as
begin
declare @x smallint, @y smallint, @z bit
set @string = replace(@string, ' ', '@')
set @x = len(@string)
set @y = 0
set @z = 1
while @y < @x
    begin
    set @y = @y + 1
    if substring(@string, @y, 1) between '0' and '9'
        continue
    else
        begin
        set @z = 0
        break
        end
    end
return (@z)
end

Money can't buy happiness -- but somehow it's more comfortable to cry in a Corvette than in a Yugo.

 

[Kliot]

Thanks guys for the fast and thurough response.

Merry Christmas to all.

 

[ESquared]

genomon, your function can be replaced with this, which will perform many times faster:

WHERE ColumnToTest NOT LIKE '%[^0-9]%'

And if IsNumeric is giving you fits because of all the things it allows, try doing it with like statements:

WHERE
  ColumnToTest NOT LIKE '%[^0-9.-]%'
  AND ColumnToTest NOT LIKE '%.%.%'
  AND ColumnToTest NOT LIKE '%-%-%'
  AND (
     ColumnToTest LIKE '-%'
     OR ColumnToTest NOT LIKE '%-%'
  )
  AND (
      ColumnToTest LIKE '%[0-9].[0-9]%'
      OR ColumnToTest NOT LIKE '%.%'
  )

I'd be willing to bet the speed of that against any user-defined function that uses a loop and substrings.

 

[genomon]

THANKS!!!!



Money can't buy happiness -- but somehow it's more comfortable to cry in a Corvette than in a Yugo.