13. Character strings and functions on strings

STRING FUNCTIONS Most C libraries provide functions that operate on strings. Let's take a look at four of the most useful and common: strlen(), strcat(), strcmp(), and strcpy(). We have already used the strlen() function, which finds the length of a string.

Subroutines for working with characters and strings function Chr(a: byte): char; Converts a code to a Windows-encoded character function ChrUnicode(a: word): char; Converts a code to a Unicode character function OrdUnicode(a: char): word; Converts a character to Unicode code function UpperCase(ch: char): char;

Color Functions Type colors Color is a synonym for System.Drawing.Color. function RGB(r,g,b: byte): Color; Returns a color that contains the red (r), green (g), and blue (b) components (r,g and b - ranging from 0 to 255) function ARGB(a,r,g,b: byte): Color; Returns the color that contains

We continue to study the language SQL queries and today we will talk about string SQL functions . We will look at basic and commonly used string functions such as: LOWER, LTRIM, REPLACE and others, we will consider everything, of course, with examples.

SELECT name || surname AS FIO FROM table

Or to separate with a space enter

those. two vertical bars combine two columns into one, and to separate them with a space, I put a space between them ( any character can be used, such as dash or colon) in apostrophes and combined also with two vertical bars (Transact-SQL uses + instead of two vertical bars).

INITCAP function

Next comes a very useful function, INITCAP- which returns the value in the string in which each word begins with capital letter, but continues small. This is necessary if you do not follow the rules for filling in one or another column and in order to display the whole thing in nice view you can use this function, for example, in your table there are entries in the name column of the following form: IVAN Ivanov or Peter Petrov, you use this function.

SELECT INITCAP (name) AS FIO FROM table

And you get it like this.

UPPER function

A similar function, only returning all capitalized characters, is UPPER.

SELECT UPPER (name) AS FIO FROM table

• name – column name;
• 20 - number of characters ( field length);
• ‘-‘ is the character to be padded to the required number of characters.

RPAD function

Let's take a look at the inverse function. RPAD- the action and syntax is the same as for LPAD, only the characters on the right are complemented ( in LPAD on the left).

SELECT RPAD (name, 20, "-") AS name FROM table

LTRIM function

Next comes also in some cases a useful function, LTRIM- this function removes the leftmost characters you specify. For example, you have a “city” column in your database, in which the city is indicated as “Moscow”, and there are also cities that are indicated simply as “Moscow”. But you need to display the report only in the form of "Moscow" without "city", but how to do this if there are such and such? You're just specifying a kind of pattern "g." and if the leftmost characters start with "r", then those characters will simply not be output.

SELECT LTRIM (city) AS gorod FROM table

This function looks at the characters on the left, if there are no characters according to the pattern at the beginning of the line, then it returns the original value of the cell, and if there are, it deletes them.

RTRIM function

Also, let's look at the inverse function right away. RTRIM– the same as LTRIM only characters are searched on the right.

Note! In Transact-SQL, the RTRIM and LTRIM functions remove spaces from the right and left, respectively.

REPLACE function

Now consider such an interesting function as REPLACE- it returns a string in which all matches of characters are replaced with your characters that you specify. What can it be used for, for example, you have columns in the database in which there are some separator characters, let's say "/". For example, Ivan / Ivanov, and you would like to display Ivan-Ivanov, then write

SELECT REPLACE (name, "/", "-") FROM table

and you will have a character substitution.

This function replaces only the full match of characters, if for example you specify "-" i.e. three dashes, it will only look for three dashes, and it will not replace each individual dash, unlike the following function.

TRANSLATE function

TRANSLATE is a string function that replaces all characters in a string with the characters you specify. Based on the name of the function, you can guess that this is a full line feed. The difference between this function and REPLACE is that it replaces each character you specify, i.e. you have three characters, let's say abc and using TRANSLATE you can replace it with abc so you have a=a, b=b, c=c and by this principle all matches of characters will be replaced. And if you replaced with REPLACE, then you were looking for only a complete match of the characters abc located in a row.

SUBSTR function

SUBSTR – given function, returns only the range of characters you specify. In other words, let's say a string of 10 characters, but you don't need all ten, but let's say you need only 3-8 ( third to eighth). With this function, you can easily do this. For example, you have some fixed-length identifier in the database (like: AA-BB-55-66-CC) and each combination of characters means something. And at one fine moment you were told to display only 2 and 3 combinations of characters, for this you write a query of the following form.

SELECT SUBSTR (ident, "4", "8") FROM table

those. we output all characters from 4 to 8, and after this query you will get this:

LENGTH function - string length

The following function may also come in handy, this is LENGTH- which simply counts the number of characters in a string. For example, you need to find out how many characters in each cell of the column "name" is allowed, the table is as follows.

after this request you will get this.

Here we are with you and examined the main SQL string functions. In the following articles, we will continue our study of SQL.

To others. It has the following syntax:

CONV(number,N,M)

Argument number is in the number system with base N. The function converts it to the number system with base M and returns the value as a string.

Example 1

The following query translates the number 2 from decimal system calculus to binary:

SELECT CONV(2,10,2);

Result: 10

To convert the number 2E from hexadecimal to decimal, a query is required:

SELECT CONV("2E",16,10);

Result: 46

Function CHAR() translates ASCII code into strings. It has the following syntax:

CHAR(n1,n2,n3..)

Example 2

SELECT CHAR(83,81,76);

Result: SQL

The following functions return the length of a string:

• LENGTH(string);
• OCTET_LENGTH(string);
• CHAR_LENGTH(string);
• CHARACTER_LENGTH(string).

Example 3

SELECT LENGTH("MySQL");

Result: 5

It happens sometimes useful feature BIT_LENGTH(string), which returns the length of the string in bits.

Example 4

SELECT BIT_LENGTH("MySQL");

Result: 40

Substring Functions

A substring is usually a part of a string. Often you want to know the position of the first occurrence of a substring in a string. There are three functions that solve this problem in MySQL:

• LOCATE(substring, string [,position]);
• POSITION(substring, string);
• INSTR(string, substring).

If the substring is not contained in the string, then all three functions return 0. The INSTR() function differs from the other two in the order of its arguments. The LOCATE() function may contain a third argument position, which allows you to search for a substring in a string not from the beginning, but from the specified position.

Example 5

SELECT LOCATE("Topaz", "Topaz");

Result: 31

SELECT POSITION("Topaz", "Topaz");

Result: 31

SELECT INSTR("Topaz",'Topaz');

Result: 31

SELECT LOCATE("Topaz", "Topaz Plant and LLC Topaz", 9);

Result: 20

SELECT LOCATE("Diamond", "Topaz");

Result: 0

Functions LEFT(line, N) and RIGHT(string, N) return the leftmost and rightmost N characters in the string, respectively.

Example 6

SELECT LEFT("MySQL DBMS", 4);

Result: DBMS

SELECT RIGHT("MySQL DBMS", 5);

Result: MySQL

Sometimes you need to get a substring that starts with some given position. The following functions are used for this:

• SUBSTRING(string, position, N);
• MID(string, position, N).

Both functions return N characters of the given string, starting at the given position.

Example 7

SELECT SUBSTRING("MySQL DBMS is one of the most popular DBMS", 6,5);

Result: MySQL

When working with email addresses and site addresses is a very useful function SUBSTR_INDEX(). The function has three arguments:

SUBSTR_INDEX(string, delimiter, N).

The N argument can be positive or negative. If it is negative, then the function finds the Nth occurrence of the delimiter, counting from the right. Then it returns the substring located to the right of the found delimiter. If N is positive, then the function finds the Nth occurrence of the delimiter to the left and returns the substring to the left of the found delimiter.

Example 8

SELECT SUBSTRING_INDEX("www.mysql.ru",".",2);

Result: www.mysql

SELECT SUBSTRING_INDEX("www.mysql.ru",".",-2);

Result: mysql.ru

Function REPLACE(string, substring1, substring2) allows you to replace all occurrences of substring1 in a string with substring2.

Here is a complete list of string manipulation functions taken from BOL:

Let's start with two mutually inverse functions - ASCII and CHAR.

The ASCII function returns the ASCII code of the leftmost character of the string expression that is the function's argument.

For example, here is how you can determine how many different letters there are that begin ship names in the Ships table:

It should be noted that a similar result can be obtained more simply using one more function - LEFT, which has the following syntax:

LEFT (<string expression>, <integer expression>)

and strips the number of characters given by the second argument from the left of the string given by the first argument. So,

And here is how, for example, you can get a table of codes for all alphabetic characters:

For those who are not yet aware of the generation of a numerical sequence, I refer to the corresponding article.

As you know, the codes of lowercase and uppercase letters are different. Therefore, in order to get full set without rewriting the query, just add a similar one to the above code:

I believe that it will not be difficult to add this letter to the table, if required.

Consider now the problem of finding the desired substring in a string expression. Two functions can be used for this - CHARINDEX and PATINDEX. Both of them return the starting position (the position of the first character of the substring) of the substring in the string. The CHARINDEX function has the syntax:

CHARINDEX ( search_expression, string_expression[, start_position])

Here is an optional integer parameter start_position specifies the position in the string expression from which to search search_expression. If this parameter is omitted, the search is performed from the beginning string_expression. For example, a request

It should be noted that if the searched substring or string expression is NULL, then the result of the function will also be NULL.

The following example determines the positions of the first and second occurrences of the character "a" in the name of the ship "California"

Note that when defining the second character in the function, the starting position is used, which is the position of the character following the first letter "a" - CHARINDEX("a", name)+1. The correctness of the result - 2 and 10 - is easy to check :-).

The PATINDEX function has the syntax:

PATINDEX("% sample%" , string_expression)

The main difference between this function and CHARINDEX is that the search string can contain wildcards - % and _. In this case, the trailing signs "%" are mandatory. For example, using this function in the first example would look like

The result of this query looks like this:

The fact that we get an empty result set as a result means that there are no such ships in the database. Let's take a combination of values ​​- the class and the name of the ship.

Joining two string values ​​into one is called concatenation, and in SQL Server for this operation the sign "+" is used (in the standard "||"). So,

And if the string expression will contain only one letter? The query will bring it up. This can be easily verified by writing

Sql String Functions

• CONCAT(str1,str2...) Returns a string created by concatenating arguments (arguments are specified in brackets - str1,str2...). For example, our Vendors table has a City column and an Address column. Let's say we want Address and City to be in the same column in the resulting table, i.e. we want to combine data from two columns into one. To do this, we will use the CONCAT() string function, and as arguments we will specify the names of the combined columns - city and address:

  SELECT CONCAT(city, address) FROM vendors;

  
  

  Note that the merging happened without splitting, which is not very readable. Let's tweak our query so that there is a space between the columns being joined:

  SELECT CONCAT(city, " ", address) FROM vendors;

  
  

  As you can see, a space is also considered an argument and is specified separated by commas. If there were more columns to be combined, then it would be irrational to specify spaces each time. In this case one could use the string function CONCAT_WS(separator, str1,str2...), which places a separator between the strings to be concatenated (the separator is specified as the first argument). Our request would then look like this:

  SELECT CONCAT_WS(" ", city, address) FROM vendors;

  The result did not change externally, but if we combined 3 or 4 columns, then the code would be significantly reduced.



• INSERT(str, pos, len, new_str) Returns the string str in which the substring starting at position pos and having a length of len characters has been replaced by the substring new_str. Suppose we decide in the column Address (address) not to display the first 3 characters (abbreviations street, etc.), then we will replace them with spaces:

  SELECT INSERT(address, 1, 3, " ") FROM vendors;

  
  

  That is, three characters, starting from the first, are replaced by three spaces.



• LPAD(str, len, dop_str) Returns the string str left padded with dop_str to length len. Let's say we want supplier cities to be displayed on the right, with dots filling the empty space:

  SELECT LPAD(city, 15, ".") FROM vendors;

  
  


• RPAD(str, len, dop_str) Returns the string str right-padded with dop_str to length len. Suppose we want supplier cities to be displayed on the left, and the empty space filled with dots:

  SELECT RPAD(city, 15, ".") FROM vendors;

  
  

  Note that the len value limits the number of characters to be printed, i.e. if the city name is longer than 15 characters, it will be truncated.



• LTRIM(str) Returns the string str with all leading spaces removed. This string function is useful for correctly displaying information in cases where random spaces are allowed during data entry:

  SELECT LTRIM(city) FROM vendors;



• RTRIM(str) Returns the string str with all trailing spaces removed:

  SELECT RTRIM(city) FROM vendors;

  In our case extra spaces was not, therefore, we will not see the result outwardly.



• TRIM(str) Returns the string str with all leading and trailing spaces removed:

  SELECT TRIM(city) FROM vendors;



• LOWER(str) Returns the string str with all characters converted to lower case. It does not work correctly with Russian letters, so it's better not to use it. For example, let's apply this function to the city column:

  SELECT city, LOWER(city) FROM vendors;

  
  See what abracadabra turned out. But with Latin everything is in order:

  SELECT LOWER("CITY");

  
  


• UPPER(str) Returns the string str with all characters converted to upper case. It is also better not to use Russian letters. But with Latin everything is in order:

  SELECT UPPER(email) FROM customers;

  
  


• LENGTH(str) Returns the length of the string str. For example, let's find out how many characters are in our supplier addresses:

  SELECT address, LENGTH(address) FROM vendors;

  
  


• LEFT(str, len) Returns len of the left characters of str. For example, let only the first three characters be displayed in supplier cities:

  SELECT name, LEFT(city, 3) FROM vendors;

  
  


• RIGHT(str, len) Returns len of the right characters of str. For example, let only the last three characters be displayed in supplier cities: SELECT LOAD_FILE("C:/proverka");
  Please note that you must specify the absolute path to the .