💎🤖SQL Reloaded: Copilot Stories (Part X: Working with the Left Function)

Left is one of the dull string functions in SQL Server, returning the left part of a character string with the specified number of characters. Besides mid, right and a few other functions, Left can help retrieve the subparts of a string (see previous post). This post evaluates the answers provided by Microsoft 365 Copilot in the use of the respective function.

Disclaimer: The below code was generated by Copilot via prompts and is used for demonstration purposes!

Prompt:
Give me 10 scenarios with examples in which the Left function can be used in SQL Server.

Output:

-- Example 1: Extracting Initials
SELECT LEFT('John', 1) + LEFT('Doe', 1) AS Initials -- Result: 'JD'

-- Example 2: Getting the First N Characters of a String:
SELECT LEFT('Hello World', 5) AS Substring; -- Result: 'Hello'

-- Example 3: Extracting wear from a Date string
SELECT LEFT('2025-03-06', 4) AS Year; -- Result: '2025'

-- Example 4: Truncating a string for display:
SELECT LEFT('This is a long description', 10) + '...' AS ShortDescription; -- Result: 'This is a ...'

-- Example 5: Extracting Country Code from Phone Number:
SELECT LEFT('+49 1234567890', 3) AS CountryCode; -- Result: '+49'

-- Example 6: Getting the First Word of a Sentence:
SELECT LEFT('Hello World', CHARINDEX(' ', 'Hello World') - 1) AS FirstWord;-- Result: 'Hello'

-- Example 7: Formatting Product Codes:
SELECT LEFT(ProductCode, 3) AS ProductPrefix
FROM Products; -- Result: 'ABC' for ProductCode 'ABC12345'

-- Example 8: Extracting File Extension:
SELECT LEFT(FileName, CHARINDEX('.', FileName) - 1) AS FileExtension
FROM Files; -- Result: 'txt' for FileName 'document.txt'

-- Example 9: Creating Abbreviations:
SELECT LEFT('International Business Machines', 3) AS Abbreviation; -- Result: 'Int'

-- Example 10: Extracting Date Part from DateTime:
SELECT LEFT(CONVERT(VARCHAR, GETDATE(), 120), 10) AS DatePart; -- Result: '2025-03-06'

Example 1: Extracting the initials can prove to be useful when the space in a report is limited and one wants to have the initial of the person who created or updated the record, respectively who is responsible for a part of the process involved. One can also combine an initial with the full name.

Example 2: Getting the first word from a sentence is useful when an encoding can be derived from it. However, it's seldom that the first characters that form the encoding have a fixed number of letters. One can use the technique from Example 6.

Example 3: Getting the year or other date part from a string can be done in this way, at least if the dates are always in the expected format. It's recommended to get the date parts via the DatePart or DateName functions. 

Example 4: This is a generalization of Example 2.

Example 5: One can get the country code from a phone number if the string is correctly delimited, which is seldom the case. Sometimes, one can use the Country field from the address to identify the correct code from the phone number, though there can also be exceptions.

Example 6: This is usually the recommended way to extract the string before a delimiter and this technique has applicability in many scenarios. 

Example 7: Product numbers and other similar encodings are formed via general rules and the formula should take the rules into account.

Example 8: This technique is used quite often in combination with file names and other similar encodings.

Example 9: The rules of abbreviations are usually more complex than this, though this may work occasionally. 

Example 10: That's a valid way to extract the date from a string that contains also the time. One should consider the alternatives as well.

Prompt:
Give me 10 more scenarios with examples in which the Left function can be used in SQL Server.

Output:

-- Example 11: Extracting the First N Digits of a Number:
SELECT LEFT(CAST(1234567890 AS VARCHAR), 5) AS FirstDigits; -- Result: '12345'

-- Example 12: Trimming a String to a Fixed Length:
SELECT LEFT('This is a test string', 10) AS TrimmedString; -- Result: 'This is a '

-- Example 13: Extracting the First Part of an Email Address:
SELECT LEFT('user@example.com', CHARINDEX('@', 'user@example.com') - 1) AS Username; -- Result: 'user'

-- Example 14: Getting the First N Characters of a Column Value:
SELECT LEFT(ColumnName, 8) AS ShortenedValue
FROM TableName;-- Result: 'Example' for ColumnName 'ExampleValue'

-- Example 15: Extracting the First Few Characters of a GUID:
SELECT LEFT(NEWID(), 8) AS ShortGUID; -- Result: '550e8400'

-- Example 16: Example 11: Creating a Shortened Version of a URL:
SELECT LEFT('https://example.com/page', 15) + '...' AS ShortURL; -- Result: 'https://example...'

-- Example 17: Extracting the First N Characters of a File Path:
SELECT LEFT('C:\Users\Name\Documents\File.txt', 12) AS ShortPath; -- Result: 'C:\Users\Nam'

-- Example 18: Getting the First N Characters of a Description:
SELECT LEFT(Description, 20) AS ShortDescription
FROM Products; -- Result: 'This is a product de'

-- Example 19: Extracting the First N Characters of a JSON String:
SELECT LEFT('{"name":"John","age":30}', 10) AS ShortJSON; -- Result: '{"name":"J'

-- Example 20: Creating a Shortened Version of a Log Entry:
SELECT LEFT(LogEntry, 50) + '...' AS ShortLog
FROM Logs; -- Result: 'Error: Unable to connect to the database. Please...'

Example 11: The example makes sense especially when the first n characters have a special meaning. Just selecting n characters from a random numbers brings little benefit, though there might also be exceptions out there.

Example 12: This is similar with example 3.

Example 13: Extracting the first part of an email can be useful for further processing especially when the emails have a predefined form (e.g. first name and last name, usually delimited by a special character).

Example 14: The example is a variation of 13 but with applicability to a given column from a table.

Example 15: It may look like a way to generate unique values, though no part of the GUID is unique when taken in isolation from any other part!

Example 16: This can be used to extract the hostname of a URL.

Example 17: This technique can be used to provide the main path to a folder, though more complex rules to generate it usually apply.

Example 18: Descriptions tend to be long enough to provide identification information for the products. Therefore, selecting only long enough parts of a description can help minimize the volume of data stored, which can be useful in reports of any type. 

Example 19: The technique can be used indeed, though it has little value without the addition of further information (see first example).

Example 20: If Log entries tend to be real long, this can help extracting the essential information. It can allow further on to aggregate the information and identify the number of events stored in the log. 

Not all the examples are representative for the use of the Left function and even if it looks that they repeat themselves, the context brings slight differences. They can be met in practice in combination with CharIndex, date or similar functions. 

Happy coding!

Previous Post <<||>> Next Post