• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

Microsoft SQL - Count varchar where max len = n

Hello -

Hopefully an easy one for you.  I have a varchar(7) field in which the majority of the values are 7 digit numbers ('#######').  

I'm trying to count the number of records that have a "valid" 7 digit number value in them.  

I tried:

SELECT COUNT(myField) FROM myTable HAVING MAX(LEN(myField)) ='7'
That returned the count of all records in my table.  I know there are some blanks and commas and things in there so I know that's not right - Maybe I should use rtrim/ltrim?  

Then after a little research I found "DATALENGTH" and tried:

SELECT COUNT(myField) FROM myTable WHERE DATALENGTH(myField) ='7'
That returned a count smaller than the total record count (more than half).  This seems realistic but I'm not sure if I'm getting what I'm asking for.  

What would be the best way to go about this?

Thanks!!!
0
ttist25
Asked:
ttist25
4 Solutions
 
Surendra NathTechnology LeadCommented:
ok you can try the below one

select count(1) from myTable where len(myField) = 7

Open in new window

0
 
Lee SavidgeCommented:
SELECT COUNT(myField) FROM myTable where LEN(myField) =7

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I'm trying to count the number of records that have a "valid" 7 digit number value in them.
Define 'valid'.  Seven characters, all numbers?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT COUNT(myField) FROM myTable WHERE myfield like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
CREATE TABLE #foo (myField varchar(7) )

INSERT INTO #foo (myField) 
VALUES 
   ('1234567'), ('123,567'), ('  12345'), ('12345  '), ('1234567'), ('(23456)'),
   ('42'), ('one'), (NULL), ('BR549'), ('OU812'), ('foo')

-- Here's your seven numbers
SELECT * FROM #foo WHERE myField LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

-- Here's everything else
SELECT * FROM #foo WHERE myField NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Open in new window

0
 
ttist25Author Commented:
Thanks for the responses everyone.  I always forget how powerful "LIKE" can be.  

Anyway - I ended up using a combination of LEN(myField) = 7 and NOT LIKE '[0-9] ...' which may redefine what I consider a "valid" value to be (this is all discovery stage analysis).  

Thanks again everyone.  Always good to know you're there!
0
 
ttist25Author Commented:
Jim!  VH fan????  :D
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes, and I have a couple of sample data sets that use as customers Sammy Hagar, Eddie Van Halen, Joe Satriani, etc.
0
 
ttist25Author Commented:
Hah!  That's great.  I'm a player myself and have recently started building pedals for fun.  My standard test for the sound quality of a prototype pedal has become the opening bars from Drop Dead Legs.  

Ok - that's probably enough off-topic garbage but I had to acknowledge the connection.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In that case check out this question but do me a favor and don't answer it right away, as I want to see how the other yahoos do.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now