As a programmer, it’s important to have a solid understanding of low-level structures, even with the advancements in modern tools. One concept that seems to be overlooked is the carriage return. In this article, we will explore what a carriage return is and how to work with it in SQL Server.

What is a Carriage Return?

A carriage return is a control character that is used to reset the position of the cursor to the beginning of a line. It is represented by different codes, such as decimal, hex, and binary. While it may seem like a trivial concept, understanding carriage returns is crucial when working with formatted strings in SQL Server.

Working with Complex Strings

In today’s world, data feeds can come from various sources and may have different formats. As a DBA, you may encounter situations where you need to extract specific information from these strings and store them in your database. Let’s consider a simple example:

We need to be sure that this purchase order is processed:
34030
Please check on the status

In this case, we want to extract the purchase order number. While it may seem like a simple task, it can become complicated if the input files are inconsistent or have varying whitespace. However, with a few techniques in T-SQL, we can easily solve this problem.

Using Substring and Charindex

To extract the purchase order number, we can use the SUBSTRING and CHARINDEX functions in SQL Server. These functions allow us to identify markers in the string and extract the desired portion. Here’s an example query:

SELECT 
  CHARINDEX(':', msg),
  CHARINDEX('Please', msg),
  SUBSTRING(msg, CHARINDEX(':', msg) + 1, CHARINDEX('Please', msg) - CHARINDEX(':', msg) - 1)
FROM OrderStatusCheck

This query will give us the position of the colon and the “Please” markers, as well as the substring between them. However, we still need to handle whitespace and convert the substring to an integer.

Handling Whitespace and Converting to Integer

To remove whitespace, we can use the LTRIM and RTRIM functions. However, these functions do not remove carriage returns. To remove them, we can use the REPLACE function. Here’s an example query:

SELECT 
  CAST(REPLACE(SUBSTRING(msg, CHARINDEX(':', msg) + 1, CHARINDEX('Please', msg) - CHARINDEX(':', msg) - 1), CHAR(13) + CHAR(10), ' ') AS INT) AS 'PO'
FROM OrderStatusCheck

This query replaces the carriage returns with a space and then converts the substring to an integer. Now we have the purchase order number without any whitespace or carriage returns.

Conclusion

Understanding carriage returns and how to work with them in SQL Server is essential for handling complex strings. While modern tools may abstract away low-level concepts, having this knowledge can be invaluable when dealing with data feeds from various sources. By using functions like SUBSTRING, CHARINDEX, REPLACE, and CAST, you can easily extract and manipulate the desired information.

Hopefully, this article has provided you with a refresher on ASCII codes and a glimpse into the early days of programming. Remember, it’s important to have a solid foundation in low-level concepts, even in today’s high-level programming environments.

References: ASCII Codes, REPLACE, SUBSTRING, CHARINDEX