Converting Text to number

Hello there
I have table with field which is a text field that provide numbers and text
I would like to drop all the records that on the current field has text and
leave only the records with the number on the field
For this i've tried to use the convertion process but it gave me an error
when the data is text
Is there a way to know if the data is text or number so i can deal with it?
any help would be useful
roy@.atidsm.co.il
' 050-7709399"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in
news:#Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl:

> Hello there
> I have table with field which is a text field that provide numbers and
> text
> I would like to drop all the records that on the current field has text
> and leave only the records with the number on the field
> For this i've tried to use the convertion process but it gave me an
> error when the data is text
> Is there a way to know if the data is text or number so i can deal with
> it?
Try ISNUMERIC(expression)|||Although ISNUMERIC will provide the desired results in many cases, it will
also consider some obscure numeric values to be numeric. You can use LIKE
to test for integer digits. The example shows some differences between
ISNUMERIC and LIKE.
CREATE TABLE MyTable
(
MyNumber varchar(10)
)
GO
INSERT INTO MyTable VALUES('')
INSERT INTO MyTable VALUES(' ')
INSERT INTO MyTable VALUES('1')
INSERT INTO MyTable VALUES(' 2')
INSERT INTO MyTable VALUES('3 ')
INSERT INTO MyTable VALUES('1D')
INSERT INTO MyTable VALUES('0D1')
INSERT INTO MyTable VALUES('0E1')
SELECT *
FROM MyTable
WHERE ISNUMERIC(MyNumber) = 0
SELECT *
FROM MyTable
WHERE MyNumber LIKE '%[^0-9]%' OR
DATALENGTH(MyNumber) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl...
> Hello there
> I have table with field which is a text field that provide numbers and
> text
> I would like to drop all the records that on the current field has text
> and
> leave only the records with the number on the field
> For this i've tried to use the convertion process but it gave me an error
> when the data is text
> Is there a way to know if the data is text or number so i can deal with
> it?
> any help would be useful
> --
>
> roy@.atidsm.co.il
> ' 050-7709399
>|||ISNUMERIC also interprets '.', '-' and '+' as numeric.
Dan Guzman wrote:
> Although ISNUMERIC will provide the desired results in many cases, it
> will also consider some obscure numeric values to be numeric. You
> can use LIKE to test for integer digits. The example shows some
> differences between ISNUMERIC and LIKE.
> CREATE TABLE MyTable
> (
> MyNumber varchar(10)
> )
> GO
> INSERT INTO MyTable VALUES('')
> INSERT INTO MyTable VALUES(' ')
> INSERT INTO MyTable VALUES('1')
> INSERT INTO MyTable VALUES(' 2')
> INSERT INTO MyTable VALUES('3 ')
> INSERT INTO MyTable VALUES('1D')
> INSERT INTO MyTable VALUES('0D1')
> INSERT INTO MyTable VALUES('0E1')
> SELECT *
> FROM MyTable
> WHERE ISNUMERIC(MyNumber) = 0
> SELECT *
> FROM MyTable
> WHERE MyNumber LIKE '%[^0-9]%' OR
> DATALENGTH(MyNumber) = 0
>
> "Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
> news:%23Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl...