Search Char in SQL query

[jaawaad]

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

[Tony Rogerson]

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
<ja*****@gmail.comwrote in message
news:11**********************@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]

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
<ja*****@gmail.comwrote in message
news:11**********************@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]

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
<ja*****@gmail.comwrote in message
news:11**********************@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

[Erland Sommarskog]

(ja*****@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, es****@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]

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:

(ja*****@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, es****@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

[Erland Sommarskog]

(ja*****@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, es****@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

[Madhivanan]

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

Madhivanan
Erland Sommarskog wrote:

(ja*****@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, es****@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