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?
JyozealAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.