Extract Numeric Records From Character Type

Question

Post reply

Extract Numeric Records From Character Type

cdun2

SSCertifiable

Points: 7893
More actions
February 16, 2007 at 12:50 pm

#122989

Hello,
I've got a column in a SQL Server 2000 table that is CHAR(2). The data can be strictly alpha, or stricly numeric, or alphanumeric. I need to find the records where the data in the column is strickly numeric.
I thought I could easily find something if I Googled for it, but no luck.
If you have any ideas on this, please let me know.
Thank you for your help!
CSDunn

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

cdun2 SSCertifiable Points: 7893 More actions · Answer 1

It occured to me to use ASCII;

select

tc from

(

select

distinct

tc

,

ascii(lower(tc)) as Lcase,

ascii(upper(tc)) as Ucase,

(ascii(lower(tc))) - (ascii(upper(tc))) as Cdifference

from

salesCommissions.dbo.DailyAccountsDownload

where

tc not in (select distinct tc from salesreporting.dbo.salesTCList)

)

as a

where

Cdifference = 0

--********************

Does anyone have an alternate suggestion?

Thanks again.

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 2

If your only working on char(2) the idea below is fairly straight-forward.

create table #tmp( tc char(2))

insert into #tmp

select '11' union all

select '1a' union all

select 'a1' union all

select '1 ' union all

select '1b' union all

select '91'

select *

from #tmp

where left(tc,1) between '0' and '9'

and right(tc, 1) between '0' and '9'

Malcolm Wynden Say Hey Kid Points: 678 More actions · Answer 3

SELECT CharField

FROM YourTable

WHERE PATINDEX('[0-9][0-9]',CharField) > 0

This uses a wildcard range of [0-9] for both the first and second characters of the CHAR field - and if both characters are "hits" (ie in the 0-9 range), then the PATINDEX function will return the starting point of this string (which will be 1). Otherwise, PATINDEX will return 0.

This query will return the numeric only CharField values.

PATINDEX can be pretty useful in parsing string values, especially with the wildcard functions.

cdun2 SSCertifiable Points: 7893 More actions · Answer 4

cdun2

SSCertifiable

Points: 7893

February 17, 2007 at 4:30 am

#690358

Thank you for your help!

CSDunn

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

This simple test works with any length of string:

declare @t table ( c varchar(20))

insert into @t 
select '11' union all
select '1a' union all
select 'a1' union all
select '1 ' union all
select '1b' union all
select '1222245b2' union all
select '12222 572' union all
select '123456789012 ' union all
select '1b' union all
select '91'

select * 
from  @t
where c like '%[^0-9]%'

Results:

c                    
-------------------- 
1a
a1
1 
1b
1222245b2
12222 572
123456789012 
1b

(8 row(s) affected)

Malcolm Wynden Say Hey Kid Points: 678 More actions · Answer 6

Nice touch Michael - a good generalization of the solution (though it does need an extra NOT in there to get the right result set)

select * from @t where NOT c like '%[^0-9]%'

cdun2 SSCertifiable Points: 7893 More actions · Answer 7

cdun2

SSCertifiable

Points: 7893

February 19, 2007 at 9:19 am

#690508

Thanks again!

CSDunn

Elaine Shafer-378548 Newbie Points: 1 More actions · Answer 8

how about:

create table #tmp( tc char(2))

insert into #tmp

select '11' union all

select '1a' union all

select 'a1' union all

select '1 ' union all

select '1b' union all

select '91'

select * from #tmp

where ISNUMERIC(tc) = 1

drop table #tmp

/**********results************/

tc

11

1

91

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 9

Short and sweet, however SQL considers characters like . to be numeric.

create table #tmp( tc char(2))

insert into #tmp

select '11' union all

select '1a' union all

select 'a1' union all

select '1 ' union all

select '1b' union all

select '91' union all

select '5.'

select * from #tmp

where ISNUMERIC(tc) = 1

11

1

91

5.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

Oh no, no, no... MJV had it right and ISNUMERIC does NOT mean ISALLDIGITS!

create table #tmp( tc VARCHAR(10))

insert into #tmp

select '0d01' union all

select '0e01' union all

select 'odoa' union all

select '$1,000.00 ' union all

select '3/5' union all

select '+2' union all

select '-2'

select * from #tmp

where ISNUMERIC(tc) = 1

Please see the following for more detailed information...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=243646&p=2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)