Writing a query that returns only certain data in a column

[ali31]

I have 1 table (client_matters) and 1 view (vw_cli_comm_details) that I have joined together on a column named matter_code.

In vw_cli_comm_details the comm_no column contains a mixture of phone numbers, email addresses, fax numbers etc.

I want my query to return all entries in the client_matters table, but only return comm_no from vw_cli_comm_details if it contains an @.
Basically I want to see the comm_no only if we have an email address for the corresponding client in the client_matters table/view, otherwise I want this column to be blank for that particular row.

What is the syntax I should be using to do this?

Thanks in advance

Ali

 

[gmmastros]

You can use case/when to do this. Something like....

Select Columns....,
[blue]       Case When CharIndex('@', comm_no) > 0 
            Then comm_no
            Else ''
            End As EmailAddress[/blue]
From   YourTables....

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

SELECT ID, CASE WHEN VAL LIKE '%@%' THEN VAL ELSE '' END Val 
FROM(
SELECT '1' ID ,'aaa' VAL UNION ALL
SELECT '2' ID ,'bbb' VAL UNION ALL
SELECT '3' ID ,'a@a' VAL UNION ALL
SELECT '4' ID ,'b@b' VAL UNION ALL
SELECT '5' ID ,'@' VAL) as A

HTH,
Lodlaiden

A lack of experience doesn't prevent you from doing a good job.

 

[ali31]

That's worked perfectly.

Thanks both for your help