Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

All records where there is a value

Hello

I am trying to retrieve all records where there is only a value in the T_Customers.Postcode with the following query, but it doesn't give me the right result, can anyone help?

SELECT     T_Customers.CustomerID, T_Customers.CustTypeID, T_Customers.Surname, T_Customers.Forename, T_Customers.TitleID, T_Customers.Ref1,
                      T_Customers.Add1, T_Customers.Add2, T_Customers.Add3, T_Customers.Add4, T_Customers.County, T_Customers.Postcode
                   
FROM         T_Customers
WHERE  exists ( select * from T_Customers where T_Customers.Postcode ='');
0
M_Boy76
Asked:
M_Boy76
1 Solution
 
Patrick MatthewsCommented:
SELECT     T_Customers.CustomerID, T_Customers.CustTypeID, T_Customers.Surname, T_Customers.Forename, T_Customers.TitleID, T_Customers.Ref1,
                      T_Customers.Add1, T_Customers.Add2, T_Customers.Add3, T_Customers.Add4, T_Customers.County, T_Customers.Postcode
                   
FROM         T_Customers
WHERE T_Customers.Postcode IS NULL
0
 
tigin44Commented:
Try this


SELECT     T_Customers.CustomerID, T_Customers.CustTypeID, T_Customers.Surname, T_Customers.Forename, T_Customers.TitleID, T_Customers.Ref1,
                      T_Customers.Add1, T_Customers.Add2, T_Customers.Add3, T_Customers.Add4, T_Customers.County, T_Customers.Postcode
                   
FROM         T_Customers
WHERE  T_Customers.Postcode IS NOT NULL;
0
 
M_Boy76Author Commented:
Both queries returned records where the post codes are empty?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Patrick MatthewsCommented:
M_Boy76,

1) Please try restating what you are looking for

2) Is the Postcode column going to be NULL, or a zero-length string?  They are NOT the same thing :)

Regards,

Patrick
0
 
M_Boy76Author Commented:
I have some records that have postcodes, in the format xx11 2km, and some of these records dont have post codes,.

Now what i want to do is retrieve all records where the record has a post code.
0
 
Patrick MatthewsCommented:
M_Boy76 said:
>>Now what i want to do is retrieve all records where the record has a post code.

Did you try tigin44's suggestion?  That ought to do it...
0
 
BrandonGalderisiCommented:
What about this:



SELECT     T_Customers.CustomerID, T_Customers.CustTypeID, T_Customers.Surname, T_Customers.Forename, T_Customers.TitleID, T_Customers.Ref1, 
                      T_Customers.Add1, T_Customers.Add2, T_Customers.Add3, T_Customers.Add4, T_Customers.County, T_Customers.Postcode
                   
FROM         T_Customers 
WHERE len(isnull(T_Customers.Postcode,'')>0

Open in new window

0
 
ZberteocCommented:
How about this:
SELECT     
	T_Customers.CustomerID, 
	T_Customers.CustTypeID, 
	T_Customers.Surname, 
	T_Customers.Forename, 
	T_Customers.TitleID, 
	T_Customers.Ref1, 
	T_Customers.Add1, 
	T_Customers.Add2, 
	T_Customers.Add3, 
	T_Customers.Add4, 
	T_Customers.County, 
	T_Customers.Postcode
FROM
	T_Customers 
WHERE 
	isnull(ltrim(rtrim(T_Customers.Postcode)),'')<>''

Open in new window

0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now