How to filter the columns?

Question

Using SQL Server 2005

I want to filter the column where column value not equal to numeric

Table1

Status

010203
Absent
231415
Ramesh
Suresh

...,

I want to get only Names from the status column like

Tried Query

Select Status from table1 where status <> 'absent' and status <> numecivalue

How to mentioned status <> numericvalue

Expected Output

Table

Status

Ramesh
Suresh
...,

Need Query Help.

Answer 1

Try ISNUMERIC(expression).

Answer 2

Use:

SELECT t.status 
  FROM table1 t 
 WHERE t.status != 'absent' 
   AND ISNUMERIC(t.status) != 1

Reference: ISNUMERIC

Answer 3

SELECT 
    status 
FROM 
    table1 
WHERE 
    status  <> 'absent' AND 
    ISNUMERIC(status) <> 1

Answer 4

Have a look at this weblos ..............

http://weblogs.sqlteam.com/mladenp/archive/2007/10/18/SQL-Server-Filtering-Numeric-data-from-a-character-based-column.aspx

CREATE FUNCTION dbo.IsNumericEx( @value nvarchar(max) )
RETURNS BIT
AS
BEGIN
         DECLARE @isInt BIT

         SELECT  @isInt = 1 
         WHERE   @value NOT LIKE '%[^0-9.-]%'
                 AND LEN(REPLACE(@value, '.', '')) >= LEN(@value) - 1
                 AND CHARINDEX('-', @value) IN (0, 1)
                 AND CHARINDEX('-', @value, 2) = 0

         RETURN ISNULL(@isInt, 0)
END
GO

DECLARE @t1 TABLE (title varchar(20))
INSERT INTO @t1
SELECT '123d456' UNION ALL
SELECT '12 3456' UNION ALL
SELECT '123456' UNION ALL
SELECT '1234-56' UNION ALL
SELECT '123456-' UNION ALL
SELECT '-123456' UNION ALL
SELECT '-123-456' UNION ALL
SELECT 'dddfaf56' UNION ALL
SELECT '5532.673' UNION ALL
SELECT '5532673.' UNION ALL
SELECT '.5532.673'

SELECT   *
FROM     @t1
WHERE    dbo.IsNumericEx(title) = 0

SELECT   *
FROM     @t1
WHERE    dbo.IsNumericEx(title) = 1

GO
DROP FUNCTION dbo.IsNumericEx

Answer 5

You can use RegEx on your query.

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx