need help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need help

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2012-01-19 : 11:41:42

Hi All.

I would like to create query by join two table.


select
pr.ProviderID
,FName + ' ' + LName ProviderName
,CASE WHEN LEN(ContactInfo) < 6 THEN CentralizeStaff.dbo.fn_FormatExt(ContactInfo)
	WHEN LEN(ContactInfo) < 11 THEN CentralizeStaff.dbo.fn_FormatPhone(ContactInfo) end Phone
from dbo.Provider pr
left outer join dbo.ProviderContact c
on c.ProviderID = pr.ProviderID

One table has ProviderID, FName, LName.
10 BBB IUYIUYII
18 AAA IUYIUYIYIU

Second table ProviserID(10,18), ContactInfo.
2 18 1273 1111111111
4 10 1273 22222
5 10 1274 ssa@dsa.org
6 10 1272 9171234562

The field ContactInfo has phone number and email address data. How to join those table and get ProviderID, FName, LName, ContactInfo but display in ContactInfo only phone number?

Thanks.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 11:45:14

Hello eugz,

You will have to parse out the pertinent information from ContactInfo. Can you provide some sample data?

HTH.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 12:00:41

Thanks for updating,

Something as simple as a filter (looking for '@' might be used. Something like:


SELECT <fields>, CASE WHEN ContactInfo LIKE '%@%' THEN ContactInfo ELSE '' END AS ContactInfo
FROM table1 t1
LEFT JOIN table2 t1 on t2.ID = t1.ID

- or something as complex as a email validation udf can be applied to filter only those records which contain email addresses. Many are available online by searching "Validate email SQL Function"

HTH.

eugz
Posting Yak Master

210 Posts

Posted - 2012-01-19 : 12:23:59

Hi ehorn.

My query gave same result. I would like to eliminate record if ContactInfo has email. For instance, if we got
10 BBB IUYIUYII 22222
10 BBB IUYIUYII
10 BBB IUYIUYII 9171234562

I would like to have
10 BBB IUYIUYII 22222
10 BBB IUYIUYII 9171234562

If is it possible how it get?

Thanks.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 12:28:34

quote:
Originally posted by eugz

Hi ehorn.

My query gave same result. I would like to eliminate record if ContactInfo has email. For instance, if we got
10 BBB IUYIUYII 22222
10 BBB IUYIUYII
10 BBB IUYIUYII 9171234562

I would like to have
10 BBB IUYIUYII 22222
10 BBB IUYIUYII 9171234562

If is it possible how it get?

Thanks.

Sure, you could use NOT LIKE and place it as a filter. It is not sargable and will cause a scan over the table however.

Or you could use something like:


SELECT d.*
FROM
(
	SELECT 
		<fields>, 
		CASE WHEN ContactInfo LIKE '%@%' THEN 0 ELSE 1 END AS Include
	FROM table1 t1
	LEFT JOIN table2 t1 on t2.ID = t1.ID
) d

WHERE d.Include = 1

HTH.

eugz
Posting Yak Master

210 Posts

Posted - 2012-01-19 : 12:39:26

Now we eliminate record but phone number not displaying

Thanks.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 12:42:12

Good to hear,

Do you have ContactInfo in your SELECT list of fields to display?

If so, please post your statement so other can have a look.

HTH.

eugz
Posting Yak Master

210 Posts

Posted - 2012-01-19 : 12:51:48