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

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.

10 digit   0010010001
11 digit   00010010001
  • 2
1 Solution
Scott PletcherSenior DBACommented:
WHERE LEN(accountnumber) = 11
WHERE LEN(accountnumber) > 10 --just in case
Pawan KumarDatabase 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

Your query needs like below-

WHERE datalength(accountnumber) = 11
WHERE datalength(accountnumber) > 10
Scott PletcherSenior 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.
jamesmetcalf74Author Commented:
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: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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