Ignore matching values when using a case when statement

Basssque
Basssque used Ask the Experts™
on
Is there a way that I can specify that I'd like to ignore any instances of the following in this case when statement?
)
(
1-
anything following the letter x

case when length(CONTACTS.CNT1_CPHONE) != 12 then CNT1_CPHONE||'  <-- invalid_length' else CNT1_CPHONE end CNT1_CPHONE
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I do not understand what you are asking.

What following the letter x?
What do you want changed in the case statement?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

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

Author

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Brian CroweDatabase Administrator
Top Expert 2005

Commented:
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

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

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

Author

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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
/

Open in new window



The results:
:555-555-5555:
:555-555-5555:
:555-555-5555:
:555-555-5555:

Open in new window

David VanZandtOracle Database Administrator III

Commented:
Back to you original question, I use either the upper() or lower() string function to make a comparison case-insensitive.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Where was case sensitivity part of the original question?

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

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Why not rethink what it is that you are doing?

The next "bad" data I can think of is:  
555 555 5555
1 555 555 5555 x55

Why not use this:
trim(regexp_replace(CONTACTS.CNT1_CPHONE,'^1[- ]|x.*$|[() ]|[-]'))

and check for a length of 10.


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

Author

Commented:
Works absolutely perfect!  Thanks so much!!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial