Re: Search Char in SQL query

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@xxxxxxxxx> wrote in message
news:1152546853.551581.273620@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: disk I/O tool
    ... SQL Server MVP ... SET NOCOUNT ON; ... DECLARE @DT DATETIME; ...
    (microsoft.public.sqlserver.server)
  • Re: A Query Question
    ... declare @rep table, ... fetch next from elist into @eid, ... set nocount off ...
    (microsoft.public.sqlserver.programming)
  • Re: Querying a database
    ... Use My Database ... Declare @sCnt VarChar ... Set Nocount Off ... Dim pairsAdapter As New MWFNTableAdapters.MWFNTableAdapter ...
    (microsoft.public.vb.database)
  • re: Querying a database
    ... Alter Procedure spMyTest ... Declare @sCnt VarChar ... Declare @qry VarChar ... Set Nocount Off ...
    (microsoft.public.vb.database)
  • Re: Querying a database
    ... Alter Procedure spMyTest ... Declare @sCnt VarChar ... Declare @qry VarChar ... Set Nocount Off ...
    (microsoft.public.vb.database)