（只是一個(gè)隨手記錄，暫時(shí)不翻譯了，復(fù)制粘貼到微信里長(zhǎng)按就可以翻譯哈）

我問（對(duì)不起那天我不太禮貌??）：show me an example of sql scripts that retrieve the last six digit of a column of numbers and returns ?six zero if the number is shorter than six digits

ChatGPT：

Sure! Here's an example of an SQL script that retrieves the last six digits of a column of numbers and returns six zeroes if the number is shorter than six digits:

In this script, replace column_name with the name of the column containing the numbers you want to extract the last six digits from, and table_name with the name of the table containing that column.

Here's how this script works:

1. The LENGTH() function is used to determine the length of the value in column_name.

2. If the length is less than six, the REPEAT() function is used to create a string of zeroes with a length of six minus the length of the value in column_name. The CONCAT() function is then used to concatenate this string of zeroes with the value in column_name.

3. If the length is six or greater, the RIGHT() function is used to extract the last six characters of the value in column_name.

4. The CASE statement is used to apply one of these two operations to each value in the column, depending on its length.

5. The result of this operation is returned as a column named last_six_digits.

我問：what if the column contains letters, i.e., I want to retrieve the last six character including numbers and letters. It might be useful to use regular expression

?

ChatGPT：You're right, if the column contains letters as well as numbers, you'll need to use regular expressions to extract the last six characters. Here's an example SQL script that uses regular expressions to extract the last six characters from a column containing alphanumeric values:

?

?

In this script, replace column_name with the name of the column containing the alphanumeric values you want to extract the last six characters from, and table_name with the name of the table containing that column.

?

Here's how this script works:

?

The LENGTH() function is used to determine the length of the value in column_name.

If the length is less than six, the REPEAT() function is used to create a string of zeroes with a length of six minus the length of the value in column_name. The CONCAT() function is then used to concatenate this string of zeroes with the value in column_name.

If the length is six or greater, the REGEXP_REPLACE() function is used to extract the last six characters of the value in column_name using a regular expression.

The regular expression used in this script is .*(.{6})$, which matches any number of characters (.*) followed by the last six characters of the string ((.{6})$). The parentheses around .{6} capture these last six characters as a group.

The \\1 in the replacement string tells the function to replace the entire string with the captured group (i.e., the last six characters).

The CASE statement is used to apply one of these two operations to each value in the column, depending on its length.

The result of this operation is returned as a column named last_six_chars.