Ignore matching values when using a case when statement

I do not understand what you are asking.

What following the letter x?
What do you want changed in the case statement?

"length" and || indicate Oracle (most likely).  Pls remove the "SQL Server" tags.

The CNT1_CPHONE field contains entries such as
555-555-5555
1-555-555-5555
(555)-555-5555
555-555-5555 x55
etc

x stands for extension
I want to validate that the field length is 12 characters like 555-555-5555 but don't want to specify that format exactly because the purpose of the statement is to output values that are incorrect so we can fix them.
so I want to ignore
1-
(
)
and anything including and after the letter x

It sounds like you need a function to clean up phone field values.

DECLARE @Phone		VARCHAR(50) = '(801) 123-4567 x9876';

SELECT @Phone =
	RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(
	CASE
		WHEN CHARINDEX('x', @Phone, 1) > 0 THEN SUBSTRING(@Phone, 1, CHARINDEX('x', @Phone, 1) - 1)
		ELSE @Phone
	END, '(', ''), ')', ''), ' ', ''), '-', '')))

SELECT @Phone

Select allOpen in new window

If Oracle, try this:
trim(regexp_replace(cnt1_cphone,'^1-|x.*$|[()]'))

With the regexp_replace(cnt1_cphone,'^1-|x.*$|[()]') statement
Is there a way to specify 1- exclusively?  I want to retain any other hyphens if they exist in the event that there are additional hyphens that need to be removed when the output is reviewed.

>>Is there a way to specify 1- exclusively?

Did you run your sample data through what I posted?  What I posted does that.  The ^1- means a 1 followed by a '-' at the start of the string.

Is this only for Oracle?

Here is my complete test case I used:

with mydata as (
select '555-555-5555' cnt1_cphone from dual
union all
select '1-555-555-5555' cnt1_cphone from dual
union all
select '(555)-555-5555' cnt1_cphone from dual
union all
select '555-555-5555 x55' cnt1_cphone from dual
)
select ':' || trim(regexp_replace(cnt1_cphone,'^1-|x.*$|[()]')) || ':' from mydata
/

Select allOpen in new window

The results:

:555-555-5555:
:555-555-5555:
:555-555-5555:
:555-555-5555:

Select allOpen in new window

Back to you original question, I use either the upper() or lower() string function to make a comparison case-insensitive.

Where was case sensitivity part of the original question?

If it is, regexp can be told to ignore case.

case sensitivity is not a concern
not sure I did this right, I'm missing an expression somewhere


SELECT
STUDENTS.STUDENT_NUMBER,
STUDENTS.LAST_NAME,
STUDENTS.FIRST_NAME,
STUDENTS.SCHOOLID,
STUDENTS.GRADE_LEVEL,

with mydata as (
select '555-555-5555' CONTACTS.cnt1_cphone from dual
union all
select '1-555-555-5555' CONTACTS.cnt1_cphone from dual
union all
select '(555)-555-5555' CONTACTS.cnt1_cphone from dual
union all
select '555-555-5555 x55' CONTACTS.cnt1_cphone from dual
)
select ':' || trim(regexp_replace(cnt1_cphone,'^1-|x.*$|[()]')) || ':'

FROM
"SCHEMA"."STUDENTS" "STUDENTS", "mydata"
LEFT JOIN "SCHEMA"."CONTACTS" "CONTACTS" ON "STUDENTS"."DCID" = "CONTACTS"."STUDENTSDCID"
WHERE
(LENGTH(CONTACTS.CNT1_CPHONE) !=12 AND
(CONTACTS.CNT1_CPHONE IS NOT NULL) and (CONTACTS.CNT1_CPHONE != ' '))

GROUP BY
STUDENTS.STUDENT_NUMBER,
STUDENTS.LAST_NAME,
STUDENTS.FIRST_NAME,
STUDENTS.SCHOOLID,
STUDENTS.GRADE_LEVEL,
CONTACTS.CNT1_CPHONE

Please let us know if this is only for Oracle or if you also need SQL Server.


>>not sure I did this right, I'm missing an expression somewhere

Don't use the WITH statement.  That was just showing you my test case.

Just use the REGEXP_REPLACE in the query you already have.

If you have:
case when length(CONTACTS.CNT1_CPHONE) != 12 then CNT1_CPHONE||'  <-- invalid_length' else CNT1_CPHONE end CNT1_CPHONE

It will become:
case when length(trim(regexp_replace(CONTACTS.CNT1_CPHONE,'^1-|x.*$|[()]')) ) != 12 then CNT1_CPHONE||'  <-- invalid_length' else CNT1_CPHONE end CNT1_CPHONE

Ok, so this statement is close, I just need to remove 1- if those two characters exist in that order.
case when length(trim(regexp_replace(CONTACTS.CNT1_CPHONE,'x()')) ) != 12 then CNT1_CPHONE||'  <-- invalid_length' else CNT1_CPHONE end CNT1_CPHONE

Why are you not using exactly what I posted?

What I posted removes the '1-' from the beginning of a string.

If what I posted doesn't work for some of your data, please post more sample data and expected results.

You are right, I shouldn't have done that
It works

the last thing I need to do is replace any instances of (111) with 111-
111 being any 3 consecutive digits within parenthesis trailed by either a space or another number only

so
(111)111-1111 which is 13 characters
or
(111) 111-1111 which is 14 characters
gets changed to
111-111-1111

Works absolutely perfect!  Thanks so much!!!!