• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3876
  • Last Modified:

how to check numeric,alphabetic and alphanumeric in TSQL

In a SP i would like to check if the given parameter is alphabetic , numeric or alphanumeric. The parameter can be two or 3 words together  and can contain email ids so space,'@' and '- 'should be accepted as valid value for alphabetic and alphanumeric. So below are valid values

'abc'
'123'
'a1b1'
'1a1b'
'windows - 1primary'
'windows@  primary'
'123 657'
''a1 b1'
for all special characters it should display none.

'12 -21' and '^$**' are some of invalid values for which none is displayed. I cannot do this in UI need to do in SP only.

below is what i wrote
if( ( @filter not like '%[0-9]%') and( @filter not like '%[^A-Za-z]-@ %') and (@filter like '%[^!~#$''&*]%'))
select 'alphabetic'
else if(@filter not like '%[A-Za-Z]%')
      begin
                     if(isnumeric(@filter)=1) select 'numeric' else select 'non numeric'
       end
else if(( @filter not like '%[^a-Z][0-9]-@'' ''%') and( not(@filter not like '%[^[0-9]- %')) )
    select 'alphanumeric'
else select 'none'

Open in new window


Is this ok? or any better way available?
0
Jyozeal
Asked:
Jyozeal
  • 3
  • 2
  • 2
  • +1
1 Solution
 
lcohanDatabase AnalystCommented:
use ISNUMERIC sql function that returns TRUE(or 1) when the column value IS a NUMBER and 0 otherwise

https://msdn.microsoft.com/en-us/library/ms186272(v=sql.105).aspx
0
 
Scott PletcherSenior DBACommented:
What is the logic behind both:
'windows - 1primary'
'123 657'
being ok, but:
 '12 -21'
is not OK?
0
 
lcohanDatabase AnalystCommented:
For letters only - no numbers you can use something like:

SELECT * FROM mytable
WHERE mycol LIKE '%[a-zA-Z]%' and mycol NOT LIKE '%[0-9]%'

and you can combine the three ISNUMERIC = 0 (or 1) plus the above in a CASE statement.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JyozealAuthor Commented:
Thanks for your replies.
Scottpletcher,
windows - 1primary' - is valid entry for one of the columns
'123 657' - numeric values with spaces is valid entry for another column
being ok, but:
 '12 -21'  - few columns contain only numeric data but defined as varchar, from valid data point of view numeric values allow spaces but not any other character
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Are you asking if your code is ok?
I've tested it with you examples and this is what was returned. Please confirm if all options have the right type or point the one that is wrong and what should be expected:
 'abc'  --> alphabetic
 '123'  --> numeric
 'a1b1' --> none
 '1a1b' --> none
 'windows - 1primary' --> alphanumeric
 'windows@  primary'  --> alphabetic
 '123 657' --> non numeric
 'a1 b1'   --> none
 '12 -21'  --> non numeric
 '^$**'    --> alphabetic
0
 
JyozealAuthor Commented:
Vitor Montalvão, i did test them. i only wanted to know if this code covers everything and if there is any better way of achieving the same. Thanks for your reply.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I only want you to confirm that what I posted is right since I think isn't clear for us and that's why I think no one replied you for 4 days.
If we don't understand what you want we can't help you.
0
 
JyozealAuthor Commented:
Vitor Montalvão, i got what you are saying now. Since i already figured it out may be i will close this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now