Brain lock on determine if char or not

Question

Post reply

Brain lock on determine if char or not

timscronin

SSCertifiable

Points: 6851
More actions
June 26, 2009 at 9:32 am

#139207

I have an issue where a programmer has an insert sp which is is a varchar(9) where the first 2 characters can be alpha or int, but the remaining must be numbers. Did not want to do rule because there app has issues with handling error code. THe data is trapped in a variable defined as a varchar, I am having a brain lock on determining how to check for only numbers in the remaining 7 characters.

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

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

isnumeric(right(myfield,7))

or if it's not always 7 digits

isnumeric(substring(myfield,3,7) )

MVDBA

Mark Cowne One Orange Chip Points: 26953 More actions · Answer 2

Use LIKE

declare @s-2 varchar(9)

--set @s-2='ab1234567'

set @s-2='ab123456x'

if @s-2 not like '__[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

print 'Not Numeric'

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

David McKinney SSChampion Points: 10472 More actions · Answer 3

michael vessey (6/26/2009)
isnumeric(right(myfield,7))
or if it's not always 7 digits
isnumeric(substring(myfield,3,7) )

Got to be a bit careful with isnumeric as ISNUMERIC('12345e1') returns 1

I think you can use something like

isnumeric(right(myfield,7) +'e1')

Regards,

David.

Mark Cowne One Orange Chip Points: 26953 More actions · Answer 4

Mark (6/26/2009)
Use LIKE
declare @s-2 varchar(9)
--set @s-2='ab1234567'
set @s-2='ab123456x'
if @s-2 not like '__[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
print 'Not Numeric'

Also for variable width string,

if substring(@s,3,7) like '%[^0-9]%'

print 'Not Numeric'

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

I still wouldn't use is numeric as "." is also included in the check for example 10.10 would be numeric.

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

The like statement will not work if the statement is less that 9 chars.

what you want to do is search for a non-numeric char starting from the 3 char in the string

try something like this:

PATINDEX('%[^0-9]%',substring(@s,3,7))

basically says find some anything that isn't 0-9

However you could change the like statement to be dynamic something this:

@s-2 not like '[0-9 a-z][0-9 a-z]' + REPLICATE('[0-9]',LEN(@s)-2)

However you'd need to test to see which is a better performing solution in environment.

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

timscronin SSCertifiable Points: 6851 More actions · Answer 7

THanks, interesting now I am being told they can trap and to use a rule. Since the rule should allow for the first 2 char of being either A-Z or number but it can be variable length is the syntax for the variable length?

CREATE RULE pattern_rule

AS

@value LIKE '_ _%[0-9]'

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 8

timscronin (6/26/2009)
THanks, interesting now I am being told they can trap and to use a rule. Since the rule should allow for the first 2 char of being either A-Z or number but it can be variable length is the syntax for the variable length?
CREATE RULE pattern_rule
AS
@value LIKE '_ _%[0-9]'

[/quote]

That solution won't work for all situations.

That will check for any 2 charaters so you could have a.123456 .

Secondly the %[0-9]

Says find me any char followed by a 0-9 value

so if you had 'aaa12345' if would not pick it up.

have you tried my patindex solution?

Also Sorry to Mark, I read you post to fast, your variable width statement works. oooops need more coffee

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 9

if you look at the original post its 7 int values - so we don't need to worry about . - e etc

MVDBA

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 10

I must have missed something but I didn't see the op defining the input. The output rule is all that was defined.

If you now the values are going to be ints then don't need to check for them, it sounds like they enter numbers or letters in which case e is a problem.

So I guess the question to ask is. Are the the possible values a user can type.

Could they type.

numbers letters or symbols?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life