Solved

show records that have character string grater than 2

Posted on 2010-11-30
3
201 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 200 total points
ID: 34240219
Just add AND LEN(STATE)>2 to your where clause
0
 
LVL 32

Accepted Solution

by:
Erick37 earned 300 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL 2005 Srink database in chunks 4 58
sql query help 2 53
How to simplify my SQL statement? 14 53
Restrict result set 1 39
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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

772 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