Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Part 7 - SQL query to find rows that contain only numerical data

Suggested Videos:
Part 4 - Delete duplicate rows in sql
Part 5 - SQL query to find employees hired in last n months
Part 6 - Transform rows into columns in sql server



Let me explain the scenario mentioned in one of the sql server interview. We have the following table.
sql query to find rows that contain only numerical data



Write a SQL query to retrieve rows that contain only numerical data. The output of the query should be as shown below.
retrieve only integer data from a column in a sql server table

SQL Script to create the TestTable
Create Table TestTable
(
     ID int identity primary key,
     Value nvarchar(50)
)

Insert into TestTable values ('123')
Insert into TestTable values ('ABC')
Insert into TestTable values ('DEF')
Insert into TestTable values ('901')
Insert into TestTable values ('JKL')

This is very easy to achieve. If you have used ISNUMERIC() function in SQL Server, then you already know the answer. Here is the query
Select Value from TestTable Where ISNUMERIC(Value) = 1

ISNUMERIC function returns 1 when the input expression evaluates to a valid numeric data type, otherwise it returns 0. For the list of all valid numeric data types in SQL Server please visit the following MSDN link.
http://technet.microsoft.com/en-us/library/ms186272(v=sql.110).aspx

sql server interview questions and answers

2 comments:

  1. we can see the result for used the database engine ISNUMERIC FUNCTION
    but my question is it how to views the result same table.
    alphabet ..... ?

    Insert into TestTable values ('123')

    Select Value from TestTable Where ISNUMERIC(Value) = 1

    Insert into TestTable values ('DEF')

    Select Value from TestTable Where isAlphabet(Value) =A


    ReplyDelete
  2. Dear Prem Kumal
    To Retrive rows which contains only alphabets in string
    Syntax
    SELECT * FROM Table1 WHERE col1 NOT LIKE '%[0-9]%'

    SELECT * FROM TestTable WHERE Value NOT LIKE '%[0-9]%'


    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.