Trailing Spaces and String Comparisons

Kevin Feasel

2019-06-19

T-SQL

Bert Wagner shows how SQL Server handles trailing spaces when comparing two strings:

The LEN() function shows the number of characters in our string, while the DATALENGTH() function shows us the number of bytes used by that string.

In this case, DATALENGTH is equal to 10. This result is due to the padded spaces occurring after the character “a” in order to fill the defined CHAR length of 10. We can confirm this by converting the value to hexadecimal. We see the value 61 (“a” in hex) followed by nine “20” values (spaces).

Click through to see what happens and why it works the way it does.

Related Posts

Computing Time to Payment on Invoices

Daniel Hutmacher has a painful but realistic problem to solve: Here’s an example customer. You’ll notice right off the bat that we’re sending this customer an invoice every day on the 20th of the month. To add some complexity, the customer will arbitrarily pay parts of the invoiced amount over time, and to add insult […]

Read More

Tracking Who Changed Data

Bert Wagner is on a quest to find out who moved his cheese: Have you ever wondered who was the last person (or process) to modify a piece of data in your database? SQL Server offers plenty of system views and functions that provide insight into how your server is running and how your queries are performing. […]

Read More

Categories

June 2019
MTWTFSS
« May Jul »
 12
3456789
10111213141516
17181920212223
24252627282930