getting length of 8 characters

Claim number (string)
CF060001
CF060001A
CF060001B
AV000001
AV000212F
AV000001F
FD232122
FD232122G
SD223213
SD223213H

I only want to get records, which have length of 8 characters.
So output will be CF060001, AV000001, FD232122, and SD223213

Anyone can help me to write this in sql?WHERE LEN(ClaimNumber) = 8

or possibly you will need

WHERE LEN(RTRIM(ClaimNumber)) = 8

Roy Harvey
Beacon Falls, CT

On 13 Dec 2006 12:55:20 -0800, "TGEAR" <ted_gear@.hotmail.comwrote:

Quote:

Originally Posted by

>Claim number (string)
>CF060001
>CF060001A
>CF060001B
>AV000001
>AV000212F
>AV000001F
>FD232122
>FD232122G
>SD223213
>SD223213H
>
>I only want to get records, which have length of 8 characters.
>So output will be CF060001, AV000001, FD232122, and SD223213
>
>Anyone can help me to write this in sql?

|||Roy Harvey (roy_harvey@.snet.net) writes:

Quote:

Originally Posted by

WHERE LEN(ClaimNumber) = 8
>
or possibly you will need
>
WHERE LEN(RTRIM(ClaimNumber)) = 8

Since len() does not count trailing blanks, rtrim is redudant here.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 13 Dec 2006 22:43:30 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Since len() does not count trailing blanks, rtrim is redudant here.

Thanks for the education! 8-)

Roy|||Roy Harvey napisal(a):

Quote:

Originally Posted by

WHERE LEN(ClaimNumber) = 8
>
or possibly you will need
>
WHERE LEN(RTRIM(ClaimNumber)) = 8

.... or in case N data, and automaticly avoid problem with spaces:

where datalength(ClaimNumber)/2 = 8

Matik|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

Roy Harvey napisal(a):

Quote:

Originally Posted by

>WHERE LEN(ClaimNumber) = 8
>>
>or possibly you will need
>>
> WHERE LEN(RTRIM(ClaimNumber)) = 8

>
... or in case N data, and automaticly avoid problem with spaces:
>
where datalength(ClaimNumber)/2 = 8

Not really sure what you mean. len() counts characters and ignores
trailing spaces, so it is an ideal function to use in this case, as
it works the same with varchar and nvarchar data. (It does not work
with text/ntext though.)

datalength on the other hand counts bytes and includes trailing spaces,
so with datalength you need to trim and you need to know whether you
are working with varchar or nvarchar data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx