469,148 Members | 1,383 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,148 developers. It's quick & easy.

Search Char in SQL query

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

Jul 10 '06 #1
7 4785
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

Jul 10 '06 #2
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
Jul 10 '06 #3
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
Jul 10 '06 #4
(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
Jul 10 '06 #5
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
Jul 10 '06 #6
(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
Jul 10 '06 #7

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
Jul 11 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by todd | last post: by
10 posts views Thread by pembed2003 | last post: by
1 post views Thread by Dave Townsend | last post: by
5 posts views Thread by pembed2003 | last post: by
1 post views Thread by heath | last post: by
1 post views Thread by Eric | last post: by
3 posts views Thread by fienen | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.