Phone and CPF fields in SQL?

Asked

Viewed 2,456 times

3

I’m wondering what type of variable to create for the CPF and Phone fields in the database, they should be saved like this:

(34) 9652-5214
134.124.214-47

With nchar wouldn’t complicate the search? But in the case of nchar, how do I put it so he can’t store letters? And how do I put it so that when the user searches, they can search only by numbers, without () etc? Only put the mask in the search field?

c# sql

  • Why record the mask?

  • I would write only the numbers and apply mask when performing the registration, viewing the query or doing a search.

  • in another form I will display a table like DataGridView from there to be showing with punctuation etc.. Or you can put on a mask? If you can then record without a mask. The problem is that you can’t put on int because variable int has short size limit

  • Save both without mask in text type field and when displaying format as you want in output

  • ok, but type text field stores letter too, have you do to read only numbers? How int and numeric?

  • You do the processing in the field that is receiving this data in c#. You will use what? TextBox even normal?

  • yes yes, just textbox

  • 3

    http://answall.com/questions/47871/tipo-do-campo-cpf-ou-cnpj-no-banco-de-dados-varchar-ou-int/47873#47873

Show 3 more comments

3 answers

3


In C# there is a component called MaskedTextBox. In it it is possible to specify a mask for the field.
For example in your phone field can be the mask: (##) #####-#### on the property Mask. He will accept only numbers. When reading the value, you will use the following code: 

this.maskedTextBox1.TextMaskFormat = MaskFormat.ExcludePromptAndLiterals;
String telefone = this.maskedTextBox1.Text;

So you will only have numbers in the String telefone, to save in the database.

  • in case I would declare the two fields of the database as nchar(14) right?

  • I didn’t even need the second line, only changing the format of the mask it already records without the mask.. Vlw

1

What is the database?

For the CPF I recommend using the Char type and specify a size of 11 characters, without saving the dots and the hyphen.

For the phone I recommend a VARCHAR field of maximum size 11, since in some regions of the country use one more field or one less field.

ALTER TABLE tabela ADD COLUMN cpf CHAR(11);
ALTER TABLE tabela ADD COLUMN telefone VARCHAR(11);

I hope this answer has helped, any doubt we are available.

  • but here’s the thing, char and varchar accept letters etc, hence if the eartip (user) record data wrong is considered system failure, have to restrict a char or varchar to read only numbers?

  • Do the server check, when the application is saving the data, if it is not done the check who will return error to the application will be the database. See about the difference between char and varchar, char always stores a fixed size for the column.

1

All checks you can do before storing in the database the information, for example a form may have mascara that adds the (xx) xxxx-xxxx, but you make a treatment to remove the other characters that are not number, this way storing only number in a varchar in the bank.

Remembering if you want the value checked before sending to confirm that it sends only correct digits, use regular expression to recognize the string something like ([0-9]{2})\s[0-9]{4}-[0-9]{4} I am not saying that this regular expression is the right one, but it is something very close to what you can use, so it will force you to have two numbers in the DDD between parentheses and 8 consecutive numbers separated by -.

This already guarantees, then a split or a tokenizer depending on what you use to separate and remove what is not a digit, and concatenates all the rest as a number and saves it in the database.

When accessing, to display the data in a visual way only create a mask again to apply on top of the restored value. As database values need to be correct, the treatments who does and always the program to take the value and demonstrate, at least the best way for you to think is this.

Browser other questions tagged c# sql

You are not signed in. Login or sign up in order to post.