Check for Numeric - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Check for Numeric

Author

Topic

Jarrod
Starting Member

3 Posts

Posted - 2005-10-05 : 17:42:48

Hello all!

I'm trying to join two tables based off an id. The problem is sometimes the id is numeric. When it is numeric one of the tables left pads it with zeros, the other does not.

So, what I need to do is check for numeric and left pad one of the columns with zeros if it is numeric.

I think I have the padding figured out, but I'm not sure how to check for numeric.

Any help or guidance would be greatly appreciated.

Thanks

-Jarrod

SamC
White Water Yakist

3467 Posts

Posted - 2005-10-05 : 22:00:02

You don't say so, but it sounds like the ID is VARCHAR.

You could use ISNUMERIC() = 1 -- To test. I'd try something else for a join.

You don't mention how many zeros are needed to left pad, so I'll assume it's 8 digits...

One possible JOIN where table A is Padded with leading zeros and B is not would be

ON A.ID = RIGHT('00000000' + B.ID, LEN(A.ID)) OR A.ID = B.ID

but this might be a problem if A.ID was 'shorter' than B.ID

removing the leading zeros from A.ID might be a better test...

ON RIGHT(A.ID, LEN(A.ID) - PATINDEX('^[0]', A.ID) + 1) = B.ID

JOINS perform really well when the indicies do not require alteration. If there were any way to correct this mismatched naming convention it would be better in the long run.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-06 : 00:42:22

>>You could use ISNUMERIC() = 1

Thats not reliable as

Select ISNUMERIC('1e23')
Select ISNUMERIC('1d3')

will return 1

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-10-06 : 04:12:19

WHERE MyVarcharColumn NOT LIKE '%[^0-9]%'

will match only values that are comprised of just the digits 0-9

Kristen

PRAVINBHANDE
Starting Member

1 Post

Posted - 2009-06-30 : 07:59:30

Hi All,

You can use following trick to check the numeric or non numeric values.

For +ve values

select * from $TABLE where ascii($VALUE) between 48 and 57

For -ve values

select * from $TABLE where ascii(abs($VALUE)) between 48 and 57

Hope You Have Got What You Want.

Pravin B.

Pravin Bhande

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-30 : 08:15:03

quote:
Originally posted by PRAVINBHANDE

Hi All,

You can use following trick to check the numeric or non numeric values.

For +ve values

select * from $TABLE where ascii($VALUE) between 48 and 57

For -ve values

select * from $TABLE where ascii(abs($VALUE)) between 48 and 57

Hope You Have Got What You Want.

Pravin B.

Pravin Bhande

What made you to post the 4 years old thread a wrong answer?


select * from 
(
	select '6534.45' as value union all select '12d2' 
) as T where ascii(VALUE) between 48 and 57

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources