SEO

I have a text field in a table that contains number along with chars.
Is there a way i can write a query to show all the fields that contains
just Numbers or Char in a field??

TBALE Example

COL1 : COL2(nvarchar)
---------------------------
100 345G01
200 123456789
300 GQ9220

I looked at ISNUMERIC first but I don't think thats what you are after, but
this will work...

set nocount on

declare @test table (
MyData nvarchar(50) not null
)

insert @test values ( 'thisoneok' )
insert @test values ( 'has123numbers' )

declare @numbers table (
digit char(1) not null
)
insert @numbers values( '0' )
insert @numbers values( '1' )
insert @numbers values( '2' )
insert @numbers values( '3' )
insert @numbers values( '4' )
insert @numbers values( '5' )
insert @numbers values( '6' )
insert @numbers values( '7' )
insert @numbers values( '8' )
insert @numbers values( '9' )

select *,
has_numbers = case when exists (
select *
from @numbers n
where len( replace( t.MyData, n.digit, '' ) ) <>
len( t.MyData )
) then 'Y' else 'N' end
from @test t

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


<jaawaad@gmail.com> wrote in message
news:1152546853.551581.273620@m73g2000cwd.googlegr oups.com...
>I have a text field in a table that contains number along with chars.
> Is there a way i can write a query to show all the fields that contains
> just Numbers or Char in a field??
>
> TBALE Example
>
> COL1 : COL2(nvarchar)
> ---------------------------
> 100 345G01
> 200 123456789
> 300 GQ9220
>

Thanks for your reply.
Actually i was looking for something like ISNUMERIC. I just wanted to
pull out rows that contains only numbers and just eliminate the others
with chars in them.
Here is what i tried so far....

Select * from Mytable where ISNUMERIC(substring(COL2,2,10))

Assuming data field looks like this

COL2
A123X456
A45687E

I only want to do the check on portion of the string so i used
substring to take out the part i wanted to test against ISNUMERIC.

It shold work, but for some reason im getting an error msg. Any idea
what am i doing wrong here?

Thanks!

Tony Rogerson wrote:
> I looked at ISNUMERIC first but I don't think thats what you are after, but
> this will work...
>
> set nocount on
>
> declare @test table (
> MyData nvarchar(50) not null
> )
>
> insert @test values ( 'thisoneok' )
> insert @test values ( 'has123numbers' )
>
> declare @numbers table (
> digit char(1) not null
> )
> insert @numbers values( '0' )
> insert @numbers values( '1' )
> insert @numbers values( '2' )
> insert @numbers values( '3' )
> insert @numbers values( '4' )
> insert @numbers values( '5' )
> insert @numbers values( '6' )
> insert @numbers values( '7' )
> insert @numbers values( '8' )
> insert @numbers values( '9' )
>
> select *,
> has_numbers = case when exists (
> select *
> from @numbers n
> where len( replace( t.MyData, n.digit, '' ) ) <>
> len( t.MyData )
> ) then 'Y' else 'N' end
> from @test t
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
>
> <jaawaad@gmail.com> wrote in message
> news:1152546853.551581.273620@m73g2000cwd.googlegr oups.com...
> >I have a text field in a table that contains number along with chars.
> > Is there a way i can write a query to show all the fields that contains
> > just Numbers or Char in a field??
> >
> > TBALE Example
> >
> > COL1 : COL2(nvarchar)
> > ---------------------------
> > 100 345G01
> > 200 123456789
> > 300 GQ9220
> >

Thanks for your reply.
Actually i was looking for something like ISNUMERIC. I just wanted to
pull out rows that contains only numbers and just eliminate the others
with chars in them.
Here is what i tried so far....

Select * from Mytable where ISNUMERIC(substring(COL2,2,10))

Assuming data field looks like this

COL2
A123X456
A45687E

I only want to do the check on portion of the string so i used
substring to take out the part i wanted to test against ISNUMERIC.

It shold work, but for some reason im getting an error msg. Any idea
what am i doing wrong here?

Thanks!

Tony Rogerson wrote:
> I looked at ISNUMERIC first but I don't think thats what you are after, but
> this will work...
>
> set nocount on
>
> declare @test table (
> MyData nvarchar(50) not null
> )
>
> insert @test values ( 'thisoneok' )
> insert @test values ( 'has123numbers' )
>
> declare @numbers table (
> digit char(1) not null
> )
> insert @numbers values( '0' )
> insert @numbers values( '1' )
> insert @numbers values( '2' )
> insert @numbers values( '3' )
> insert @numbers values( '4' )
> insert @numbers values( '5' )
> insert @numbers values( '6' )
> insert @numbers values( '7' )
> insert @numbers values( '8' )
> insert @numbers values( '9' )
>
> select *,
> has_numbers = case when exists (
> select *
> from @numbers n
> where len( replace( t.MyData, n.digit, '' ) ) <>
> len( t.MyData )
> ) then 'Y' else 'N' end
> from @test t
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
>
> <jaawaad@gmail.com> wrote in message
> news:1152546853.551581.273620@m73g2000cwd.googlegr oups.com...
> >I have a text field in a table that contains number along with chars.
> > Is there a way i can write a query to show all the fields that contains
> > just Numbers or Char in a field??
> >
> > TBALE Example
> >
> > COL1 : COL2(nvarchar)
> > ---------------------------
> > 100 345G01
> > 200 123456789
> > 300 GQ9220
> >

(jaawaad@gmail.com) writes:
> I have a text field in a table that contains number along with chars.
> Is there a way i can write a query to show all the fields that contains
> just Numbers or Char in a field??
>
> TBALE Example
>
> COL1 : COL2(nvarchar)
> ---------------------------
> 100 345G01
> 200 123456789
> 300 GQ9220

Not really sure what you are looking for, but this query returns all
rows with digits only in COL2.

SELECT col2
FROM tbl
WHERE col2 NOT LIKE '%[^0-9]%'


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I think this works for me...i tried this with success so far...

select * from Table where substring(COL2,2,6) Like '%[0-9]')

As i know a char [A-Z] will only appear at position 7 of the string. It
only returns values that are ending with a digit.


Erland Sommarskog wrote:
> (jaawaad@gmail.com) writes:
> > I have a text field in a table that contains number along with chars.
> > Is there a way i can write a query to show all the fields that contains
> > just Numbers or Char in a field??
> >
> > TBALE Example
> >
> > COL1 : COL2(nvarchar)
> > ---------------------------
> > 100 345G01
> > 200 123456789
> > 300 GQ9220
>
> Not really sure what you are looking for, but this query returns all
> rows with digits only in COL2.
>
> SELECT col2
> FROM tbl
> WHERE col2 NOT LIKE '%[^0-9]%'
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

(jaawaad@gmail.com) writes:
> Actually i was looking for something like ISNUMERIC. I just wanted to
> pull out rows that contains only numbers and just eliminate the others
> with chars in them.

isnumeric() is probably not what you want. It returns 1 if the string
can be converted to any numeric data type. For instance, try
SELECT isnumeric('1E0')


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Isnumeric is not reliable
http://aspfaq.com/show.asp?id=2390

Madhivanan


Erland Sommarskog wrote:
> (jaawaad@gmail.com) writes:
> > Actually i was looking for something like ISNUMERIC. I just wanted to
> > pull out rows that contains only numbers and just eliminate the others
> > with chars in them.
>
> isnumeric() is probably not what you want. It returns 1 if the string
> can be converted to any numeric data type. For instance, try
> SELECT isnumeric('1E0')
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx