@Phoneno varchar
@Phoneno='(123)(4520)'
how to check if @ Phoneno doesnt have number with in the arrow brackets? example
@Phoneno='()()'
-
@senthi: If one of the answers was helpful, you may want to mark it as accepted, by clicking on the green tick next to the answer. This will help you get more answers in the future should you ask further questions on Stack Overflow :)Daniel Vassallo– Daniel Vassallo2010-03-11 19:45:45 +00:00Commented Mar 11, 2010 at 19:45
Add a comment
|
4 Answers
If you want to check if the phone numbers are in the format (nnn)(nnnn) where there MUST be 3 numbers in the first part and there MUST be 4 numbers in the second part, you can do this:
if @Phoneno like '([0-9][0-9][0-9])([0-9][0-9][0-9][0-9])'
print 'ok'
else
print 'not ok'
Minor thing with ISNUMERIC method is it will allow decimals and minus sign. That can be fixed by also replacing those chars with blank (but still won't verify correct length).
1 Comment
KM.
+1, best way to validate in this situation. As for ISNUMERIC(), if you don't want numbers containing a negative or decimal point, I would replace "." and "-" with "a" (not a blank) so that the check fails.
You can remove the brackets with the REPALCE function:
DECLARE @Phoneno varchar(1000) = '(123)(4520)';
SET @Phoneno = REPLACE(REPLACE(@Phoneno, '(', ''), ')', '');
IF (ISNUMERIC(@Phoneno) = 1)
SELECT 'Phoneno not empty';
ELSE
SELECT 'Phoneno empty or invalid';
This is not easy to do with the default text functionality built into SQL Server.
If you are on SQL Server 2005 and above, you can use CLR functions - one of the more popular ones are for date manipulation and for regular expressions.
The regular expression extensions can do this.
Comments
store your data in a normalized form, have the application parse and split this so you can just store the valid numbers in the database! if you must store the "(" and ")" characters for later display, do so, but if there is no value don't store ()() store that as a null.
2 Comments
Aaronaught
Normally I would agree, but this can be pretty tricky with data like addresses and phone numbers, especially if you have to support i18n. There are so many different representations, trying to parse them all will make your head explode.
KM.
still best to parse in application front end, if there is no value then the column should be NULL not
()()