Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

show records that have character string grater than 2

Posted on 2010-11-30
3
Medium Priority
?
208 Views
Last Modified: 2012-05-10
I have a query that displays customer information, I want to display the records where the state is grater than 2 characters
SELECT     TOP (100) PERCENT CUSTNMBR, CUSTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, PHONE1
FROM         dbo.RM00101
WHERE     (NOT (ADDRESS1 = ADDRESS2)) AND (NOT (CUSTCLAS = 'OBS')) AND (NOT (CUSTCLAS = 'EMP')) AND (NOT (CUSTCLAS = 'EXP')) AND 
                      (NOT (CUSTCLAS = 'ADV')) AND (NOT (CUSTCLAS = '')) AND (NOT (CUSTCLAS = 'VEN')) AND (NOT (CUSTNMBR LIKE '%MISC%')) AND 
                      (ADRSCODE = 'PRIMARY') AND (NOT (ZIP = ' ')) AND (NOT (ZIP LIKE '%N%')) AND (NOT (ZIP = '14624')) AND (NOT (STATE = ' ')) AND (NOT (STATE = '67')) 
                      AND (NOT (ADDRESS1 = '100 airpark drive')) AND (NOT (ADDRESS1 LIKE '%PAYABLE%')) AND (NOT (ADDRESS1 LIKE 'Business%')) AND 
                      (NOT (ADDRESS1 LIKE 'Attn%')) AND (NOT (CUSTCLAS LIKE 'CF')) AND (NOT (CUSTCLAS LIKE 'FA')) AND (NOT (CUSTCLAS LIKE 'PD')) AND 
                      (NOT (CUSTCLAS LIKE 'SA')) AND (NOT (CUSTCLAS LIKE 'SD')) AND (NOT (CUSTCLAS LIKE '.')) AND (NOT (CUSTCLAS LIKE 'PA')) AND 
                      (NOT (CUSTCLAS LIKE 'DNS')) AND (NOT (CUSTCLAS LIKE 'NC')) AND (NOT (HOLD = 1)) AND (NOT (INACTIVE = 1)) AND (NOT (CITY = ' '))
ORDER BY CUSTCLAS

Open in new window

0
Comment
Question by:skull52
3 Comments
 
LVL 1

Assisted Solution

by:pjpatricio
pjpatricio earned 800 total points
ID: 34240219
Just add AND LEN(STATE)>2 to your where clause
0
 
LVL 32

Accepted Solution

by:
Erick37 earned 1200 total points
ID: 34240353
Or if you want to ignore spaces:

LEN(RTRIM(LTRIM(STATE)))>2
0
 

Author Comment

by:skull52
ID: 34241470
Thanks, Both worked but Erick's gave the best result because it ignored the spaces.
0

Featured Post

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.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
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…

886 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