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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Perfecto
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
OR
WHERE datalength(accountnumber) > 10