Solved

MSSQL QUERY - SELECT * WHERE FIELD IS NOT ALL WHITESPACE

Posted on 2011-09-27
8
235 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 167 total points
ID: 36714121
Missed a parenthesis

SELECT COUNT(*)
FROM Employees
WHERE Login= [SomeValueInATextBox]
AND len(ltrim( Login)) > 0
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 167 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 166 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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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