Solved

show records that have character string grater than 2

Posted on 2010-11-30
3
205 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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