Search 3 data fields in a table to find the first value and return a single field in a stored procedure

CASE WHEN field1 > '' THEN field1 WHEN field2 > '' THEN field2 ELSE field3 END

Hi,

It maybe cleaner to use nullif like this:

Regards
  David

use EE
go

if object_id( N'tempdb..#t', N'U' ) is not null 
	drop table #t;
	
create table #t(
	field1 varchar( 20 )
	, field2 varchar( 20 )
	, field3 varchar( 20 )
	)
;

insert #t values( '', '1234567891', '2345678966' )
	
select *
from #t
;

select coalesce( nullif( t.field1, '' ), nullif( t.field2, '' ), nullif( t.field3, '' )) as newfield
from #t t
;

Select allOpen in new window

Or, depending on specific values in non-filled fields:

CASE WHEN LEN(field1) >= 7 THEN field1 WHEN LEN(field2) >= 7 THEN field2 WHEN LEN(field3) >= 7 THEN field3 ELSE '' END

I was a little too quick on my answer.

The COALESCE function works great except that it accepted fields that had no characters but were not NULL. Some fields also contain underscores _________ instead of numbers. I need to be able to discern between an empty non-NULL field, a field that has underscores, and a field that actually contains numbers (which is the one I want). Then I need to put that value in a NEW table so I can just use a single table in my application.

Scott - I could not get the CASE statement to work - error: multi-part identifier table.field could not be bound

David - I really don't understand your answer - it's over my head.

I asked but you didn't answer:
When you say "but they are not null", how do you decide if you should return it or not?

You mention only numbers.  But is there also a minimum or maximum length?  If a field contains "1", it is valid?

What about a field that has an international dialing prefix?  What about a phone number with dashes or parenthesis?

Please post your requirements.

Hi,

nullif returns null if the two arguments are equal. So I'm comparing each field with the empty string, which will return null if the field has an empty string, and then let coalesce work on that result. That is what you posted

My code is complete and stand-alone - you should be able to run it as is in most current versions of SQL - except for the use EE at the top that is. Its my nod to ExpertsExchange.

So what I did - I created a temp table based on your supplied data, then populated it, rutnred results, then have a query which returns the results you requested, which it did.

Regards
  David

Netminder - I looked at the data and here is what I found:
Fields that I want discarded are either completely empty (but not NULL), they have underscores, or they have 10 zeros. If they contain any numbers above 0 and have 7 or 10 characters total, then that is what I want.  Sorry that I was not more clear about that.


David I will try your code - thanks for the clarification. Why were you inserting this? insert #t values( '', '1234567891', '2345678966' )
 I want the phone number values in the table.

I should also mention that in this SP I'm selecting multiple tables and fields. I just want to apply the filtering to the phone fields.

I used the same column names you did, although I didn't put the table prefix on it.

I still the logic will end up being easier to write and more readable when done with a CASE statement rather than COALESCE.

Scott - yes i changed the table.field names to the ones I'm actually using. I could not get rid of the errors.
Perhaps it would be easier to see what I'm doing if I post the actual SP that I created. You can see the commented sections of the code I tried. The COALESCE code is working except that it accepts empty fields instead of fields with numbers.

Here it is:

SELECT        elms_users.user_last_name AS salesperson, elms_users_1.user_last_name AS interior_designer, LTRIM(RTRIM(elms_leads.last_name)) + ',  ' +  LTRIM(RTRIM(elms_leads.first_name)) AS full_name, elms_leads.address_1, elms_leads.city, elms_leads.state,
                         elms_leads.zip_code, elms_jobs_type.job_name AS job_type, LTRIM(RTRIM(elms_estimates.est_no)) AS est_no,
                                                                         
                                      COALESCE(elms_leads.phone_voice, elms_leads.phone_work, elms_leads.phone_cell) AS phone1,

                                     -- CASE  WHEN elms_leads.phone_voice >''  THEN elms_leads.phone_voice
                                     --WHEN elms_leads.phone_work >'' THEN elms_leads.phone_work
                                     --ELSE elms_leads.phone_cell  END

                         elms_leads.spouse_first_name, elms_leads.spouse_last_name,

                                     COALESCE (elms_leads.spouse_phone_cell, elms_leads.spouse_phone_work, elms_leads.spouse_phone_home) AS spouse_phone,

                                      elms_id_jobs.job_cost,
                         elms_id_jobs.date_retained, elms_id_jobs.date_sold, elms_id_jobs.date_start, elms_id_jobs.date_finish, elms_id_jobs.required_items, elms_id_jobs.next_meeting, elms_id_jobs.cabinet_ordered_date,
                         elms_id_jobs.cabinet_shipped_date, elms_id_jobs.presentation_date, elms_estimates.project_manager, elms_id_jobs.checklist, elms_leads.email, elms_estimates.est_no AS job_number,
                         elms_id_status.status_name AS ID_status
FROM            elms_id_status INNER JOIN
                         elms_leads INNER JOIN
                         elms_estimates ON elms_leads.id = elms_estimates.lead_id INNER JOIN
                         elms_id_jobs ON elms_estimates.id = elms_id_jobs.estimate_id ON elms_id_status.id = elms_id_jobs.status LEFT OUTER JOIN
                         elms_users AS elms_users_1 ON elms_estimates.interior_designer = elms_users_1.id LEFT OUTER JOIN
                         elms_users ON elms_estimates.sales_person = elms_users.id LEFT OUTER JOIN
                         elms_jobs_type ON elms_estimates.job_id = elms_jobs_type.id
WHERE        (elms_estimates.status = 2)

Hmm, interesting.  I don't see anything wrong with the CASE logic except that it doesn't have a comma after it to indicate another columns follows.

empty (but not NULL), they have underscores, or they have 10 zeros. If they contain any numbers above 0 and have 7 or 10 characters total, then that is what I want.

CASE WHEN elms_leads.phone_voice LIKE REPLICATE('[0-9]', 7)
                OR elms_leads.phone_voice LIKE REPLICATE('[0-9]', 10)
          THEN elms_leads.phone_voice
          WHEN elms_leads.phone_work LIKE REPLICATE('[0-9]', 7)
                OR elms_leads.phone_work LIKE REPLICATE('[0-9]', 10)
          THEN elms_leads.phone_work
          WHEN elms_leads.phone_cell LIKE REPLICATE('[0-9]', 7)
                OR elms_leads.phone_cell LIKE REPLICATE('[0-9]', 10)
          THEN elms_leads.phone_cell
          ELSE '' END AS phone1,

That last example will allow all zeros.

Here is what I came up with:

select 
	CASE
		WHEN TRY_CAST(elms_leads.phone_voice AS INT) > 0 and len(elms_leads.phone_voice) in (7,10) and cast(elms_leads.phone_voice AS INT) > 0
			THEN elms_leads.phone_voice
		WHEN TRY_CAST(elms_leads.phone_work AS INT) > 0 and len(elms_leads.phone_work) in (7,10) and cast(elms_leads.phone_work AS INT) > 0
			THEN elms_leads.phone_work
		WHEN TRY_CAST(elms_leads.phone_cell AS INT) > 0 and len(elms_leads.phone_cell) in (7,10) and cast(elms_leads.phone_cell AS INT) > 0
			THEN elms_leads.phone_cell
		ELSE ''
	END AS phone1
from elms_leads          
          

Select allOpen in new window

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=cea4d79cc9d7748c076dfaa50f4e14d6

Scott,

SQL does not like CASE syntax, 0-9 syntax, and all the data fields are underlined in red with multi-.. could not be bound error

Netminder, same red underlines and error on the fields as described above, and SQL did not like TRY CAST - not a recognized function name.

What version of SQL Server are you using?

The fiddle link I posted goes back to 2012 and it still runs:
https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=cea4d79cc9d7748c076dfaa50f4e14d6

Which version of SQL are you on?

Seems like it must be SQL 2005 or less if CASE isn't recognized (btw, when you're that far back, you really should state that in your q).

Is it SQL 2005 or SQL 2000 (or, gack, SQL 7.0??)?

MS SQL Server 2014 is what I'm using - with VS 2017. I probably have a simple item like a comma or something out of place in how I am inserting your samples into my whole SP query.

Post the complete SQL you have and we can check for syntax issues.

Worst case, use an inline view with your SQL and wrap out SQL around it.

For example:

select 
	CASE
		WHEN TRY_CAST(elms_leads.phone_voice AS INT) > 0 and len(elms_leads.phone_voice) in (7,10) and cast(elms_leads.phone_voice AS INT) > 0
			THEN elms_leads.phone_voice
		WHEN TRY_CAST(elms_leads.phone_work AS INT) > 0 and len(elms_leads.phone_work) in (7,10) and cast(elms_leads.phone_work AS INT) > 0
			THEN elms_leads.phone_work
		WHEN TRY_CAST(elms_leads.phone_cell AS INT) > 0 and len(elms_leads.phone_cell) in (7,10) and cast(elms_leads.phone_cell AS INT) > 0
			THEN elms_leads.phone_cell
		ELSE ''
	END AS phone1
from (
     --your complete query goes here
     select phone_voice, phone_work, phone_cell from elms_leads
) elms_leads          
          

Select allOpen in new window

I tired the above and ran it alone and it did return a small number of phone numbers out of 16000 rows. I could not get it to run with the full SQL statement that I'm using in the SP. The full SELECT statement is below (it works fine in my SP now other my goal of consolidating the phone numbers into one field) and it would be very helpful to see exactly how you would add the CASE statement into my query.  

The bold text indicates the fields I am getting the phone number data from. To restate my goal, currently there are 3 data fields that may or may not contain a valid phone number. I want to find the first field that has a valid phone number, and then insert that value into a new single data field.

THANKS!

SELECT        elms_users.user_last_name AS salesperson, elms_users_1.user_last_name AS interior_designer, elms_leads.last_name, elms_leads.first_name, elms_leads.address_1, elms_leads.city, elms_leads.state,
                         elms_leads.zip_code, elms_jobs_type.job_name AS job_type, LTRIM(RTRIM(elms_estimates.est_no)) AS est_no, elms_leads.phone_voice, elms_leads.phone_work, elms_leads.phone_cell,
                         elms_leads.spouse_first_name, elms_leads.spouse_last_name, elms_leads.spouse_phone_cell, elms_leads.spouse_phone_work, elms_leads.spouse_phone_home, elms_id_jobs.job_cost,
                         elms_id_jobs.date_retained, elms_id_jobs.date_sold, elms_id_jobs.date_start, elms_id_jobs.date_finish, elms_id_jobs.required_items, elms_id_jobs.next_meeting, elms_id_jobs.cabinet_ordered_date,
                         elms_id_jobs.cabinet_shipped_date, elms_id_jobs.presentation_date, elms_estimates.project_manager, elms_id_jobs.checklist, elms_leads.email, elms_estimates.est_no AS job_number,
                         elms_id_status.status_name AS ID_status
FROM            elms_id_status INNER JOIN
                         elms_leads INNER JOIN
                         elms_estimates ON elms_leads.id = elms_estimates.lead_id INNER JOIN
                         elms_id_jobs ON elms_estimates.id = elms_id_jobs.estimate_id ON elms_id_status.id = elms_id_jobs.status LEFT OUTER JOIN
                         elms_users AS elms_users_1 ON elms_estimates.interior_designer = elms_users_1.id LEFT OUTER JOIN
                         elms_users ON elms_estimates.sales_person = elms_users.id LEFT OUTER JOIN
                         elms_jobs_type ON elms_estimates.job_id = elms_jobs_type.id
WHERE        (elms_estimates.status = 2)

I cannot test since I don't have all your tables bus see if this works:

SELECT
	elms_users.user_last_name AS salesperson,
	elms_users_1.user_last_name AS interior_designer,
	elms_leads.last_name,
	elms_leads.first_name,
	elms_leads.address_1,
	elms_leads.city,
	elms_leads.state,
	elms_leads.zip_code,
	elms_jobs_type.job_name AS job_type,
	LTRIM(RTRIM(elms_estimates.est_no)) AS est_no,
	elms_leads.phone_voice,
	elms_leads.phone_work,
	elms_leads.phone_cell,
	CASE
		WHEN TRY_CAST(elms_leads.phone_voice AS INT) > 0 and len(elms_leads.phone_voice) in (7,10) and cast(elms_leads.phone_voice AS INT) > 0
			THEN elms_leads.phone_voice
		WHEN TRY_CAST(elms_leads.phone_work AS INT) > 0 and len(elms_leads.phone_work) in (7,10) and cast(elms_leads.phone_work AS INT) > 0
			THEN elms_leads.phone_work
		WHEN TRY_CAST(elms_leads.phone_cell AS INT) > 0 and len(elms_leads.phone_cell) in (7,10) and cast(elms_leads.phone_cell AS INT) > 0
			THEN elms_leads.phone_cell
		ELSE ''
	END AS valid_phone,
	elms_leads.spouse_first_name,
	elms_leads.spouse_last_name,
	elms_leads.spouse_phone_cell,
	elms_leads.spouse_phone_work,
	elms_leads.spouse_phone_home,
	elms_id_jobs.job_cost,
	elms_id_jobs.date_retained,
	elms_id_jobs.date_sold,
	elms_id_jobs.date_start,
	elms_id_jobs.date_finish,
	elms_id_jobs.required_items,
	elms_id_jobs.next_meeting,
	elms_id_jobs.cabinet_ordered_date,
	elms_id_jobs.cabinet_shipped_date,
	elms_id_jobs.presentation_date,
	elms_estimates.project_manager,
	elms_id_jobs.checklist,
	elms_leads.email,
	elms_estimates.est_no AS job_number,
	elms_id_status.status_name AS ID_status
FROM
	elms_id_status INNER JOIN
	elms_leads INNER JOIN
	elms_estimates ON elms_leads.id = elms_estimates.lead_id INNER JOIN
	elms_id_jobs ON elms_estimates.id = elms_id_jobs.estimate_id ON elms_id_status.id = elms_id_jobs.status LEFT OUTER JOIN
	elms_users AS elms_users_1 ON elms_estimates.interior_designer = elms_users_1.id LEFT OUTER JOIN
	elms_users ON elms_estimates.sales_person = elms_users.id LEFT OUTER JOIN
	elms_jobs_type ON elms_estimates.job_id = elms_jobs_type.id
WHERE
	(elms_estimates.status = 2) 

Select allOpen in new window

Netminder,
What you just posted does execute in my SP but it only returns one number out of 75 rows returned.  There are many phone numbers that it missed.  I attached a screen shot showing the three phone columns, and the new column which only has one result.
I tried LTRIM,RTRIM on the phone columns in case the DB was padding the rows and that did not make any difference.

The attachment is missing.

Can you also post the data types of the columns?

The data type is char(14), null
phone.PNG

This works:

                                         CASE
              WHEN elms_leads.phone_voice is not null and len(ltrim(rtrim(elms_leads.phone_voice))) in (7,10) and elms_leads.phone_voice like '%[^0-9]%'
                     THEN elms_leads.phone_voice
              WHEN elms_leads.phone_work is not null and len(ltrim(rtrim(elms_leads.phone_work))) in (7,10) and elms_leads.phone_work like '%[^0-9]%'
                     THEN elms_leads.phone_work
              WHEN elms_leads.phone_cell is not null and len(ltrim(rtrim(elms_leads.phone_cell))) in (7,10) and elms_leads.phone_cell like '%[^0-9]%'
                     THEN elms_leads.phone_cell
              ELSE ''
       END AS valid_phone,


Thanks for getting me on the right track!

>>This works:

Tripple check it with sample data.

It doesn't for my test case.  It returns the '0000000' and '__________':
https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=e0205519ecc054ed22df6eb4b503c175

Netminder - your new code worked too! Same results as the code I listed above.

Verify with the sqlfiddle link I posted.  What you posted should at least return 7 or 10 zeros.  I can't explain the underscores in the fiddle but I don't see how yours removes all zeros.

I think you are right about returning the zeros. The reason it worked in my sample is because the fields that contain the zeros are from old data that would not have been picked up in my filtered data.

Thanks!