in sql server check the string contains particular values

@Phoneno varchar
@Phoneno='(123)(4520)'

how to check if @ Phoneno doesnt have number with in the arr开发者_StackOverflow社区ow brackets? example

@Phoneno='()()' 

---

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).

---

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.

---

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.