[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MSSQL QUERY - SELECT * WHERE FIELD IS NOT ALL WHITESPACE

Posted on 2011-09-27
8
Medium Priority
?
238 Views
Last Modified: 2012-06-27
Hello,
I have a school that wants to use my TimeClock application. Some of the employees use the TimeClock and others do not. The ones that do not have a maximum length varchar full of spaces in the Login field. I need to be able to write a statement like this to see if an employee exists in the database....

SELECT COUNT(*)
FROM Employees
WHERE Login= [SomeValueInATextBox]
AND Login is not completely made of spaces

Anyone have an idea how to make the fourth line work?

Thanks so much.
0
Comment
Question by:tatton777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 10

Expert Comment

by:dwe761
ID: 36714113
SELECT COUNT(*)
FROM Employees
WHERE Login= [SomeValueInATextBox]
AND len(ltrim( Login) = 0
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36714115
Sorry, I didn't see NOT.

Should be:

SELECT COUNT(*)
FROM Employees
WHERE Login= [SomeValueInATextBox]
AND len(ltrim( Login) > 0
0
 
LVL 10

Accepted Solution

by:
dwe761 earned 668 total points
ID: 36714121
Missed a parenthesis

SELECT COUNT(*)
FROM Employees
WHERE Login= [SomeValueInATextBox]
AND len(ltrim( Login)) > 0
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 668 total points
ID: 36714833
Hi,

IF you are putting some value in textbox then there is no need for second condition.

- Bhavesh
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 664 total points
ID: 36895344
As I suspect you have already discovered, the LEN() function alone is useless for this purpose as it ignores spaces trailing spaces.  So the following will produce the same result, namely 10:

SELECT LEN('John Smith'), LEN('John Smith                                        ')


A better approach is to take advantage of the fact that LEN() ignores spaces and use something like this:
SELECT *
FROM YourTable
WHERE LEN(YourColumn) <> DATALENGTH(YourColumn)    

Note: If YourColumn is nvarchar (and not varchar) you will have to modify this code.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36895346
Let's try that again to more fully comply with your names and requirements:

SELECT COUNT(*)
FROM Employees
WHERE  LEN(Login) = DATALENGTH(Login)  
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36895360
Just goes to show that there's always more to learn.
Excellent.
0
 
LVL 1

Author Closing Comment

by:tatton777
ID: 37053023
These guys were really helpful and complete in their answers. Once again Experts-Exchange community comes through with flying colors.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question