Solved

MSSQL QUERY - SELECT * WHERE FIELD IS NOT ALL WHITESPACE

Posted on 2011-09-27
8
230 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
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
 
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now