ALPHANUM datatype contains non-alphanumeric character like @#$&*

[Former Member]

Hi All,

I use ALPHANUM datatype in one table with 127 size. As per definition in Developer guide

"ALPHANUM(n) data type specifies a variable-length character string which contains alphanumeric characters"

Example :

insert into "SOMNATH"."aadata" values(to_ALPHANUM ('!@#$%')) ;


insert into "SOMNATH"."aadata" values(')(*&!@#$%') ;

Above statement executed successfully. So HANA cannot check alphanumeric char. at the time of insertion or any other reason?

Message was edited by: Tom Flanagan

[former_member222207 · Accepted solution]

Hi Somnath,

The datatype alphanum is alpha numeric. It can contain alphabets, characters and numeric.

Henceforth the special characters can be inserted into a column which is of type alphanum.

It will be possible to insert special characters into all character data types.

Can you please let know what is that you are trying to perform so that i can be of some help.

Thanks and Regards,

M.N.Adinarayanan

[Former Member · Accepted solution]

Hi Adinarayanan,

So what is difference between VARCHAR(or NVARCHAR)  and ALPHANUM?

is it only size difference?

I create dynamic application which store variable names, control name (like textbox, label) and username, which is used in front-side application(like JSP, etc).

And we follow PASAL naming standard for variable, so it contains only alpha and number, and not allow any special characters.

So for this requirement ALPHANUM is suitable?

Thanks,

Somnath A. Kadam

[former_member222207 · Accepted solution]

Hi Somnath,

The below is the difference between varchar, nvarchar and alphanum:

Varchar can have a length from 1 to 5000 and stores data as 8 bit ASCII character string.

NVarchar can have a length from 1 to 5000 and stores data as UTF -16 Unicode character string.

Alphanum can have length from 1 to 127. Additionally if an alphanum variable contains pure numeric value, it will be represented with leading zeroes.

I would prefer to use varchar to store textbox name, label, username etc. as it takes less space.

Can you please let know whether you are trying/want to prevent values with special characters not to be stored in the variable.

In that case, i would suggest you to write a procedure inside which you can check whether there are special characters in the input value, if special characters exist throw and error, if the input value is of correct format then insert it into a table.

The datatype by default will not prevent you from storing special characters.

But in the case of  integer data type, it will not allow you to enter any other value apart from number.

Thanks and Regards,

M.N.Adinarayanan

[Former Member · Accepted solution]

Hi Adinarayanan,

Thanks for reply.

So,  what is significiance of of ALPHANUM datatype?

On which case it is most usefull? and

Why give name like ALPHANUM? becase it work like VARCHAR, only 1 to 127 length.

I know i ask many questions for small thing, but it impact on naming convesion. Unlike HANA (High-Performance Analytic Appliance) from this we understand it's(HANA) working.

How can I ask question to SAP HANA developer team?

[former_member222207 · Accepted solution]

: Hi Lars, Somnath has raised a question here on data type alphanum.

Usually a variable of alphanum type will accept alphabets and numerals. But will not accept special characters.

In SAP HANA Studio, the variable or column of a table of type alphanum accepts special characters. Can you please explain why it is designed so.

Thanks and Regards,

M.N.Adinarayanan

[lbreddemann · Accepted solution]

Hey there.

ALPHANUM is not a standardized SQL data type, so it is safe to consider this to be a SAP HANA specific one.

I haven't seen it to be used anywhere in the wild yet.

But the filling of leading empty digits with zeros could come in handy with different scenarios, e.g. working with COBOL based systems, account numbers etc.

And yes, apart from numbers, you can fill in any UNICODE character - so don't stress the ALPHA... part too much.

This is not RegEx land and sheriff  [:alpha:] and deputy [:digit:] are out at a neighbors barbie.

- Lars

[former_member222207 · Accepted solution]

Hi Somnath,

If you find my answer useful atleast to come to a conclusion that character data type including alphanum will allow to store special characters, please mark this as answered and award some points, it would motivate me to answer more questions in the forums and also will move me up in the ladder.

: Thanks for the confirmation.

Thanks and Regards,

M.N.Adinarayanan

[lbreddemann · Accepted solution]

M.N.Adinarayanan

asking for points is not considered good behavior around here.

This community is primarily concerned with sharing knowledge and experiences,  not helping you "to move up the ladder".

- Lars

[former_member222207 · Accepted solution]

Hi Lars,

Thanks Lars. We all are here to share knowledge and enrich each other with ones experience. But if a question is answered, the respective post should be marked as answered so that we all can look at other posts. That is what i primarily meant. Of course points does not matter and knowledge sharing is the primarily goal.

Thanks and Regards,

M.N.Adinarayanan

[lbreddemann · Accepted solution]

Adinarayanan MN wrote:

I would prefer to use varchar to store textbox name, label, username etc. as it takes less space.

Only saw that now... really?

You would store these things in VARCHAR?

What about international alphabets?

VARCHAR stores ASCII code data and non ASCII characters as long as they fit into a double-byte.

However, as long as you don't put any multi-byte characters in the column (e.g. because your users don't need them) using NVARCHAR will not cost you a bit more memory.

Really, we're in 2014. There is no NON-UNICODE development anymore if you are interacting with real world entities (like "users" ).

- Lars

[former_member222207 · Accepted solution]

Hi Lars,

Somnath in one of his reply mentioned the below:

"I create dynamic application which store variable names, control name (like textbox, label) and username, which is used in front-side application(like JSP, etc).

And we follow PASAL naming standard for variable, so it contains only alpha and number, and not allow any special characters."

As NVARCHAR will take 16 byte to store each character, whereas VARCHAR would consume only 8 bytes per character and there is no international characters storage for his requirement, i preferred VARCHAR.

Also can you please refer this blog: Differences between varchar and nvarchar in SQL Server

Please let know in practicality whether the difference VARCHAR/NVARCHAR (sql server data types) do matters, what are potentially the impact of using the two with respect to memory, processing speed etc.

Thanks and Regards,

M.N.Adinarayanan

[former_member182302 · Accepted solution]

Hi Adinarayan,

Have a look on this discussion:

Regards,

Krishna Tangudu

[lbreddemann · Accepted solution]

Hmm... not sure about PASAL ... never heard of that one and it doesn't pop up in my search engine of choice.

But as you describe it, it seems to be a standard/convention on how to name your variables.

Limiting what type of characters can be entered into the system definitively limits the portability of your application. Something that might not be relevant today, but very likely tomorrow...

Take the user name in SAP HANA for an example. With SPS8 our development finally made the move to allow NVARCHAR characters as user names - allowing chinese, indian, thai, israeli ... users to choose names for user accounts in the alphabet that is right for them.

Concerning processing and memory consumption: I don't really see the point in bringing up a link to a MS SQL Server blog here.

MS SQL Server encodes data very differently from SAP HANA. And Oracle stores data differently than IBM DB 2 and mySQL stores data differently than PostgreSQL ....

What I can say is: as long as you don't put in "international" characters into a NVARCHAR column, memory and processing requirements are the same as for VARCHAR.

It's easy to try out.

So, using the VARCHAR instead of NVARCHAR only limits the application but won't save a lot of resources (as long as you don't actually use the extended character set)

- Lars