By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,008 Members | 2,963 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,008 IT Pros & Developers. It's quick & easy.

getting length of 8 characters

P: n/a
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?

Dec 14 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" <te******@hotmail.comwrote:
>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?
Dec 14 '06 #2

P: n/a
Roy Harvey (ro********@snet.net) writes:
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 14 '06 #3

P: n/a
On Wed, 13 Dec 2006 22:43:30 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.sewrote:
>Since len() does not count trailing blanks, rtrim is redudant here.
Thanks for the education! 8-)

Roy
Dec 14 '06 #4

P: n/a

Roy Harvey napisal(a):
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

Dec 14 '06 #5

P: n/a
Matik (ma****@sauron.xo.pl) writes:
Roy Harvey napisal(a):
>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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 14 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.