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
Is this ok? or any better way available?
'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'Is this ok? or any better way available?
What is the logic behind both:
'windows - 1primary'
'123 657'
being ok, but:
'12 -21'
is not OK?
'windows - 1primary'
'123 657'
being ok, but:
'12 -21'
is not OK?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
ASKER
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.
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.
If we don't understand what you want we can't help you.
ASKER
Vitor Montalvão, i got what you are saying now. Since i already figured it out may be i will close this.
https://msdn.microsoft.com/en-us/library/ms186272(v=sql.105).aspx