Menu
I'm creating a database in db2 and I want to add a constrain to validate whether the user inserting a valid email address, that contain %@%.% . Without luck...any advice?
318
Using the function like REGEXP_LIKE user can validate the email in MSSQL also. User can write different function in ms sql to validate mail like in oracle. AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0; The above function is used to validate the email address in Microsoft sql.
you can use varchar as your data type for email column as emails are usually composed of letters, numbers and special characters. Show activity on this post. The right value of data lenght for the email field is database-agnostic.
Steps Ensure the editor you are contacting accepts email query letters. Email the correct editor. Use an appropriate subject line. Use a formal salutation. Present your idea. Use the same format and information as you would in a written query, however try to make it more concise. Add a signature. Review your email before sending.
Here are the nuts and bolts of how to write a successful query letter: Since you are corresponding with a human being, you’ll want to open the letter with a salutation, greeting them by name as you would any other letter. No matter how many of these query letters you email out, always personalize the letter to the particular agent.
Just be careful when using the 'or' statement. It's best to use parentheses around them such as select * from table where (field like '%a%' or field like '%b%'). If you have other AND clauses your query might act like SELECT * FROM TABLE WHERE (A = 1 AND B =2 AND C = 3) OR (D =4) which is usually not what you want.
It seems like the most obvious choice to use, until you realize it requires some upfront work. Since CONTAINS relys on SQL’s full text capabilities, you’ll need to ensure those features are installed in the DBMS. Also, inorder to use on a column, the column requires a FULLTEXT index.
You can use LIKE with wildcards. See here for the wildcards on DB2.
The underscore character ( _ ) represents any single character.
The percent sign character (%) represents a string of zero or more characters.
SELECT email
FROM YourTable
WHERE email NOT LIKE '%_@__%.__%'
This will ignore the following cases (simple version for valid emails):
You can see an example in MySql in sqlfiddle.
To add it as a constraint, you do (as mustaccio said in a comment):
alter table your_table add constraint chk_email check (email like '%_@__%.__%')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
