Is Numeric in Sybase

[KiwiGuy]

Hey Guys,

Im trying to determin whether a string(between 2 strings) is Numeric or not in sybase sql eg

AB12344BC = 1234 is numeric
AB12n44BC = 12n44 is NOT numeric

Any ideas?

Ben

p.s Sybase 11

 

[JMCraig]

If the string is at a fixed position (as in your example) you could try an expression like this to see if there's a valid numeric value there:

declare @aNumber varchar(5)
select @aNumber = substring(mycolumn, 3, 5)

if ( @aNumber like '[0-9][0-9][0-9][0-9][0-9]' )
/* convert to numeric value or whatever */

Here's two examples of how to use the LIKE operator:

declare @numStr varchar (5), @nonNumStr varchar (5)
select @numStr = '12345', @nonNumStr = '123n45'
/* are all the digits numeric */
if (@numStr like '[0-9][0-9][0-9][0-9][0-9]')
select @numStr, 'yup'
else
select @numStr, 'nope'

if (@nonNumStr like '[0-9][0-9][0-9][0-9][0-9]')
select @nonNumStr, 'yes, is a number'
else
select @nonNumStr, 'no, not a number'
/* are there any letters
(and some intervening punctuation marks) ? */
if (@numStr like '%[a-Z]%')
select @numStr, 'not a number'
else
select @numStr, 'is a number'

if (@nonNumStr like '%[a-Z]%')
select @nonNumStr, 'not a number'
else
select @nonNumStr, 'is a number'

You might want to encapsulate this all in a stored procedure which, if you need to use the first pattern ([0-9]), could actually construct a string for the second operand to LIKE by the number of characters in the input string. If the second approach will work for you, then it simplifies things somewhat.

HTH,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net