# query by length of accountnumber

I have an account table that identifies the accountnumber by nvarchar.

we recently had our account numbers change from 10 digits long to 11 digits long.
most of the accounts are stilll in the 10 digit length.
how do i query the account table to show me all accountnumbers that are 11 digits long.

ie...
10 digit   0010010001
11 digit   00010010001
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior DBACommented:
WHERE LEN(accountnumber) = 11
OR
WHERE LEN(accountnumber) > 10 --just in case
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Database ExpertCommented:
Another option is datalength function.

Apart from the LEN function Microsoft also has DATALENGTH function which we can use here. Read more from - https://docs.microsoft.com/en-us/sql/t-sql/functions/datalength-transact-sql

WHERE datalength(accountnumber) = 11
OR
WHERE datalength(accountnumber) > 10
0
Senior DBACommented:
I'd strongly recommend against that.  The most efficient way to store such a value is char(11) (not varchar(11), which requires two additional bytes for the length).

If there is a trailing space that reason, or any other reason, DATALENGTH will return 11 for every value, even those that actually contain only 10 bytes:

SELECT DATALENGTH('1234567890 '), LEN('1234567890 ').
DATALENGTH('1234567890'+ SPACE(1)), LEN('1234567890'+ SPACE(1)) --same values, with more explicit space at the end.
0
Author Commented:
Perfecto
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.