Sql string functions find occurrence. SQL string functions

We continue to study the SQL query language, and today we will talk about SQL string functions... We'll cover basic and commonly used string functions such as: LOWER, LTRIM, REPLACE and others, we will consider everything, of course, by examples.

SELECT name || surname AS FIO FROM table

Or, to separate with a space, enter

SELECT name || "" || surname AS FIO FROM table

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

INITCAP function

Next is also a very useful function, INITCAP- which returns the value in a string in which each word starts with a capital letter and continues with small letters. This is necessary if you do not follow the filling rules in one column or another and in order to display the whole thing in a beautiful form, you can use this function, for example, in your table the records in the name column are of the following form: IVAN ivanov or petr Petrov, you are using this function.

SELECT INITCAP (name) AS FIO FROM table

And you will get it like this.

UPPER function

A similar function, only returns all capitalized characters, this is UPPER.

SELECT UPPER (name) AS FIO FROM table

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

RPAD function

Let's consider the inverse function right away. RPAD- the action and syntax is the same as for LPAD, only the characters on the right are padded ( in the LPAD on the left).

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

Ivan —————-

Sergey-----

LTRIM function

Next is also a useful function in some cases, LTRIM- this function removes the leftmost characters that 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 as simply “Moscow”. But you need to display the report only in the form of "Moscow" without "g." You just specify some kind of pattern "g." and if the leftmost characters start with "g", then these characters will simply not be displayed.

SELECT LTRIM (city, "city") AS gorod FROM table

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

RTRIM function

Let's also take a 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 let's look at such an interesting function as REPLACE- it returns a string in which all matches of characters are replaced with your characters that you specify. For what it can be used, for example, you have columns in your database, in which there are some separating symbols, for example "/". For example, Ivan / Ivanov, and you would like to display Ivan-Ivanov, then write

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

and you will have a replacement of symbols.

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

TRANSLATE function

TRANSLATE- a string function that replaces all characters in the string with the characters that you specify. Based on the name of the function, you might guess that this is a complete line feed. The difference between this function and REPLACE is that it replaces every character that you specify, i.e. you have three characters, let's say abc and with the help of TRANSLATE you can replace it with abc so you have a = a, b = b, c = c and according to 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 abc symbols located in a row.

SUBSTR function

SUBSTR- this function returns only the range of characters that you specify. In other words, let's say a string of 10 characters, and you don't need all ten characters, but let's say you only need 3-8 ( from third to eighth). With this function, you can easily do this. For example, you have an identifier in the database of a fixed length (such as 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 print all characters, starting with 4 and ending with 8, and after this query you will get this:

LENGTH function - string length

The next function can also come in handy, it is LENGTH- which just counts the number of characters in a line. For example, you need to find out how many characters are in each cell in a column, let's say "name", a table like this.

SELECT LENGTH (name) FROM table

after this request you will receive this.

4

6

7

Here we are with you and examined the basic SQL string functions. In future articles, we will continue our exploration of SQL.

Transact-SQL functions can be aggregate or scalar. These types of functions are discussed in this article.

Aggregate functions

Aggregate functions perform calculations on a group of column values ​​and always return a single value from those calculations. Transact-SQL supports several common aggregate functions:

AVG

Calculates the arithmetic mean of the data in a column. The values ​​being evaluated must be numeric.

MIN and MAX

Determine the maximum and minimum value of all data values ​​contained in the column. Values ​​can be numeric, string, or time (date / time).

SUM

Calculates the total of the values ​​in a column. The values ​​being evaluated must be numeric.

COUNT

Counts the number of non-null values ​​in a column. The count (*) function is the only aggregate function that does not perform calculations on columns. This function returns the number of rows (regardless of whether the individual columns contain null values).

COUNT_BIG

Similar to the count function, with the difference that it returns a BIGINT data value.

The use of regular aggregate functions in a SELECT statement will be covered in a future article.

Scalar functions

Transact-SQL scalar functions are used in the creation of scalar expressions. (A scalar function performs calculations on a single value or list of values, while an aggregate function performs calculations on a group of values ​​from multiple rows.) Scalar functions can be categorized as follows:

numeric functions;

date functions;

string functions;

system functions;

metadata functions.

These types of functions are discussed in the following sections.

Numeric functions

Transact-SQL numeric functions are mathematical functions for modifying numeric values. A list of numeric functions and their brief description is given in the table below:

Transact-SQL Numeric Functions

Function	Syntax	Description	Usage example
ABS	ABS (n)	Returns the absolute value (that is, negative values ​​are returned as positive) of the numeric expression n.	SELECT ABS (-5.320) - Returns 5.320 SELECT ABS (8.90) - Returns 8.90
ACOS, ASIN, ATAN, ATN2	ACOS (n), ASIN (n), ATAN (n), ATN2 (n, m)	Inverse trigonometric functions that calculate the inverse cosine, inverse sine, and arctangent of the value of n (for ATN2, the arctangent of n / m is calculated). The original values ​​n, m and the result are of the FLOAT data type.
COS, SIN, TAN, COT	COS (n), SIN (n), TAN (n), COT (n)	Trigonometric functions that calculate the cosine, sine, tangent, cotangent of the value of n. The result is of data type FLOAT.
DEGREES, RADIANS	DEGREES (n), RADIANS (n)	The DEGREES function converts radians to degrees, RADIANS vice versa.	SELECT DEGREES (PI () / 4) - Returns 45 SELECT COS (RADIANS (60.0)) - Returns 0.5
CEILING	CEILING (n)	Rounds a number up to a larger integer value.	SELECT CEILING (-5.320) - Returns -5 SELECT CEILING (8.90) - Returns 9
ROUND	ROUND (n, p, [t])	Rounds n to the nearest p. When p is a positive number, the fractional part of n is rounded, and when it is negative, the integer part is rounded. When using the optional argument t, the number n is not rounded, but truncated (i.e., rounded down).	SELECT ROUND (5.3208, 3) - Returns 5.3210 SELECT ROUND (125.384, -1) - Returns 130.000 SELECT ROUND (125.384, -1, 1) - Returns 120.000
FLOOR	FLOOR (n)	Rounds down to the smallest integer value.	SELECT FLOOR (5.88) - Returns 5
EXP	EXP (n)	Calculates the value of e n.
LOG, LOG10	LOG (n), LOG10 (n)	LOG (n) - calculates the natural logarithm (i.e. base e) of n, LOG10 (n) - calculates the decimal (base 10) logarithm of n.
PI	PI ()	Returns π (3.1415)
POWER	POWER (x, y)	Calculates the x y value.
RAND	RAND ()	Returns an arbitrary number of type FLOAT in the range of values ​​between 0 and 1.
ROWCOUNT_BIG	ROWCOUNT_BIG ()	Returns the number of table rows that were processed by the last Transact-SQL statement executed by the system. The return value is of type BIGINT.
SIGN	SIGN (n)	Returns the sign of the value n as a number: +1 if positive, -1 if negative.
SQRT, SQUARE	SQRT (n), SQUARE (n)	SQRT (n) - calculates the square root of n, SQUARE (n) - returns the square of the argument n.

Date functions

Date functions evaluate the corresponding parts of the date or time of an expression, or return a time interval value. The Transact-SQL supported date functions and their brief descriptions are shown in the table below:

Transact-SQL Date Functions

Function	Syntax	Description	Usage example
GETDATE	GETDATE ()	Returns the current system date and time.	SELECT GETDATE ()
DATEPART	DATEPART (item, date)	Returns the date part specified in the item parameter as an integer.	Returns 1 (January) SELECT DATEPART (month, "01/01/2012") - Returns 4 (Wednesday) SELECT DATEPART (weekday, "01/02/2012")
DATENAME	DATENAME (item, date)	Returns the date part specified in the item parameter as a character string.	Returns January SELECT DATENAME (month, "01.01.2012") - Returns Wednesday SELECT DATENAME (weekday, "02.01.2012")
DATEDIFF	DATEDIFF (item, dat1, dat2)	Calculates the difference between the two parts of the dates dat1 and dat2 and returns an integer result in the units specified in the item argument.	Returns 19 (19 years interval between dates) SELECT DATEDIFF (year, "01/01/1990", "01/01/2010") - Returns 7305 (7305 days interval between dates) SELECT DATEDIFF (day, "01/01/1990", "01/01 .2010 ")
DATEADD	DATEADD (item, n, date)	Adds the nth number of units specified in the item argument to the specified date. (The value for n can also be negative.)	Add 3 days to the current date SELECT DATEADD (day, 3, GETDATE ())

String functions

String functions manipulate column values, which are usually of character data type. The string functions supported in Transact-SQL and their brief description are shown in the table below:

Transact-SQL String Functions

Function	Syntax	Description	Usage example
ASCII, UNICODE	ASCII (char), UNICODE (char)	Converts the specified character to the corresponding ASCII integer.	SELECT ASCII ("W") - 87 SELECT UNICODE ("u") - 1102
CHAR, NCHAR	CHAR (int), NCHAR (int)	Converts ASCII code (or Unicode if NCHAR) to the corresponding character.	SELECT CHAR (87) - "W" SELECT NCHAR (1102) - "u"
CHARINDEX	CHARINDEX (str1, str2)	Returns the starting position of the occurrence of the substring str1 in string str2. If string str2 does not contain substring str1, the return value is 0	Returns 5 SELECT CHARINDEX ("morph", "polymorphism")
DIFFERENCE	DIFFERENCE (str1, str2)	Returns an integer from 0 to 4, which is the difference between the SOUNDEX values ​​of the two strings str1 and str2. The SOUNDEX method returns a number that characterizes the sound of the string. Using this method, you can identify similar-sounding strings. Works for ASCII characters only.	Returns 2 SELECT DIFFERENCE ("spelling", "telling")
LEFT, RIGHT	LEFT (str, length), RIGHT (str, length)	Returns the number of first characters of string str specified by the length parameter of LEFT and the last length characters of string str for the RIGHT function.	DECLARE @str nvarchar (30) = "Synchronization"; - Returns "Sync" SELECT LEFT (@str, 4) - Returns "zats" SELECT RIGHT (@str, 5)
LEN	LEN (str)	Returns the number of characters (not the number of bytes) in str specified in the argument, including trailing spaces.
LOWER, UPPER	LOWER (str), UPPER (str)	The LOWER function converts all uppercase letters of str1 to lowercase. Lowercase letters and other characters included in the string are not affected. The UPPER function converts all lowercase letters of str to uppercase.	DECLARE @str nvarchar (30) = "Synchronization"; - Returns "SYNCHRONIZATION" SELECT UPPER (@str) - Returns "synchronization" SELECT LOWER (@str)
LTRIM, RTRIM	LTRIM (str), RTRIM (str)	The LTRIM function removes leading spaces in the string str, RTRIM removes spaces at the end of the string accordingly.
QUOTENAME	QUOTENAME (char_string)	Returns a delimited Unicode string to convert the input string to a valid delimited identifier.	DECLARE @str nvarchar (30) = "Synchronization"; - Returns "[Sync]" SELECT QUOTENAME (@str)
PATINDEX	PATINDEX (% p%, expr)	Returns the starting position of the first occurrence of the pattern p in the specified expr, or zero if the given pattern is not found.	Returns 4 SELECT PATINDEX ("% chro%", "Synchronization")
REPLACE	REPLACE (str1, str2, str3)	Replaces all occurrences of str2 in str1 with str3.	Returns "Desynchronization" SELECT REPLACE ("Synchronization", "Synchronization", "Desynchronization")
REPLICATE	REPLICATE (str, i)	Repeats the string str i times.	Returns "aBaBaBaBaB" SELECT REPLICATE ("aB", 5)
REVERSE	REVERSE (str)	Outputs string str in reverse order.	Returns "yicazinorkhnis" SELECT REVERSE ("Synchronization")
SOUNDEX	SOUNDEX (str)	Returns the four-character soundex used to determine the similarity of two strings. Works for ASCII characters only.
SPACE	SPACE (length)	Returns a string of spaces with the length specified in the length parameter. Analogue REPLICATE ("", length).
STR	STR (f [, len [, d]])	Converts the specified floating point expression f to a string, where len is the length of the string, including decimal point, sign, numbers, and spaces (default is 10), and d is the number of decimal places to return.	Returns "3.14" SELECT STR (3.1415, 4, 2)
STUFF	STUFF (str1, a, length, str2)	Removes length characters from string str1, starting at position a, and inserts string str2 in their place.	Note in a book SELECT STUFF ("Notebook", 5, 0, "in a") - Handbook SELECT STUFF ("Notebook", 1, 4, "Hand")
SUBSTRING	SUBSTRING (str1, a, length)	Retrieves from string str, starting at position a, a substring of length length.

System functions

Transact-SQL system functions provide extensive information about database objects. Most system functions use an internal numeric identifier (ID) that is assigned to each database object when it is created. Through this identifier, the system can uniquely identify each object in the database.

The following table lists some of the most important system functions along with a brief description:

Transact-SQL System Functions

Function	Syntax	Description	Usage example
CAST	CAST (w AS type [(length)]	Converts the expression w to the specified data type, if applicable. The w argument can be any valid expression.	Will return 3 SELECT CAST (3.1258 AS INT)
COALESCE	COALESCE (a1, a2)	Returns the first non-null expression value from the expression list a1, a2, ....
COL_LENGTH	COL_LENGTH (obj, col)	Returns the length of the col column of the database object (table or view) obj.	Returns 4 SELECT COL_LENGTH ("Employee", "Id")
CONVERT	CONVERT (type [(length)], w)	Equivalent to the CAST function, but the arguments are specified differently. It can be used with any data type.
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	Returns the current date and time.
CURRENT_USER	CURRENT_USER	Returns the name of the current user.
DATALENGTH	DATALENGTH (z)	Returns the number of bytes that expression z occupies.	This query returns the length of each field SELECT DATALENGTH (FirstName) FROM Employee
GETANSINULL	GETANSINULL ("dbname")	Returns 1 if the use of null values ​​in the dbname database meets the ANSI SQL standard.
ISNULL	ISNULL (expr, value)	Returns the value of expr if it is not NULL; otherwise, value is returned.
ISNUMERIC	ISNUMERIC (expr)	Determines if expr is a valid numeric type.
NEWID	NEWID ()	Creates a unique 16-byte binary string for storing values ​​of the UNIQUEIDENTIFIER data type.
NEWSEQUENTIALID	NEWSEQUENTIALID ()	Creates a GUID that is greater than any other GUID previously generated by this function on the specified computer. (This function can only be used as the default for a column.)
NULLIF	NULLIF (expr1, expr2)	Returns null if the values ​​of expr1 and expr2 are the same.	The query returns NULL for the project - which has Number = "p1" SELECT NULLIF (Number, "p1") FROM Project
SERVERPROPERTY	SERVERPROPERTY (propertyname)	Gets information about the properties of the database server.
SYSTEM_USER	SYSTEM_USER	Returns the ID of the current user.
USER_ID	USER_ID ()	Returns the user ID username. If no user is specified, then the current user ID is returned.
USER_NAME	USER_NAME ()	Returns the username with the specified id. If no identifier is specified, the name of the current user is returned.

Metadata functions

Basically, metadata functions return information about a specified database and database objects. The table below summarizes some of the most important metadata functions, along with a brief description:

Transact-SQL Metadata Functions

Function	Syntax	Description	Usage example
COL_NAME	COL_NAME (tab_id, col_id)	Returns the name of the column with the specified col_id of the table with tab_id.	Returns the column name "LastName" SELECT COL_NAME (OBJECT_ID ("Employee"), 3)
COLUMNPROPERTY	COLUMNPROPERTY (id, col, property)	Returns information about the specified column.	Returns the value of the PRECISION property - for the Id column of the Employee table SELECT COLUMNPROPERTY (OBJECT_ID ("Employee"), "Id", "precision")
DATABASEPROPERTY	DATABASEPROPERTY (database, property)	Returns the value of the property property of the database database.	Returns the value of the IsNullConcat property - for the SampleDb database SELECT DATABASEPROPERTY ("SampleDb", "IsNullConcat")
DB_ID	DB_ID ()	Returns the database identifier db_name. If no database name is specified, then the current database identifier is returned.
DB_NAME	DB_NAME ()	Returns the name of the database with db_id. If no identifier is specified, then the name of the current database is returned.
INDEX_COL	INDEX_COL (table, i, no)	Returns the name of the indexed column of the table table. The column is indicated by the index identifier i and the position no of the column in that index.
INDEXPROPERTY	INDEXPROPERTY (obj_id, index_name, property)	Returns the properties of the named index or statistics for the specified table identification number, the name of the index or statistics, and the name of the property.
OBJECT_NAME	OBJECT_NAME (obj_id)	Returns the name of the database object identified by obj_id.	SELECT OBJECT_NAME (245575913);
OBJECT_ID	OBJECT_ID (obj_name)	Returns the object identifier obj_name of the database.	Returns 245575913 - Employee table ID SELECT OBJECT_ID ("Employee")
OBJECTPROPERTY	OBJECTPROPERTY (obj_id, property)	Returns information about objects from the current database.

Hello dear readers of the blog site. Today I would like to talk about the SQL language, and in particular about the functions for processing text. To create and manage a website, it is often not necessary to know the SQL language. Content management systems allow you to edit site content without writing queries. But at least a cursory acquaintance with the structured query language will help you significantly speed up the modification and management of data in your site's database.

I am often faced with tasks: to remove part of the text from the text fields of the database, to combine string data or something else related to the text. Doing all this through the site admin panels is very inconvenient and dreary. It's much easier to write a database query that does all of these things in a couple of seconds.

So, let's begin...

Symbolic functions in sql language

Let's start in order with the simplest. First, let's look at the ASCII string function, which is used to determine the ASCII code of text characters:

integer ASCII(str string)

The function returns an integer value - the ASCII code of the first left character of the string str. If the str string is empty, it returns 0 and NULL if the str string does not exist.

SELECT ASCII ("t");
Position: 116
SELECT ASCII ("test");
Position: 116
SELECT ASCII (1);
Position: 49

integer ORD(str string)

If the first left character of str is multibyte, it returns its code in the format: ((first byte ASCII code) * 256 + (second byte ASCII code)) [* 256 + third byte ASCII code ...]. If the first left character of the string str is not multibyte, it works as an ASCII function - it returns its ASCII code.

SELECT ORD ("test");
Position: 116

The CHAR function, which is closely related to the ASCII function, does the opposite:

string CHAR(int integer, ...)

The CHAR function returns a character string based on their ASCII codes. If NULL is found among the values, it is skipped.

SELECT CHAR (116, "101", 115, "116");
Result: "test"

SQL functions for string concatenation

One of the most popular categories of features. After all, it is often necessary to combine the values ​​of several fields of the site database tables. There are several functions in the SQL language at once for string concatenation.

CONCAT function:

string CONCAT(str1 string, str2 string,...)

The function returns a string created by concatenating the arguments. More than two arguments can be specified. If one of the arguments is NULL, then the returned result will be NULL. Numeric values ​​are converted to a string.

SELECT CONCAT ("Hello", "", "world", "!");
Result: "Hello world!"
SELECT CONCAT ("Hello", NULL, "world", "!");
Result: NULL
SELECT CONCAT ("Number pi", "=", 3.14);
Result: "Number pi = 3.14"

As you can see from the examples, strings are concatenated without separators. In order to separate words in the first example, you have to use a space as an argument. If there were more words, then it would not be very convenient to insert spaces each time.

For such cases, there is the CONCAT_WS function:

string CONCAT_WS(separator string, str1 string, str2 string,...)

The function concatenates strings like the CONCAT function, but inserts a separator between the arguments. If separator is NULL, then the result is NULL. NULL string arguments are skipped.

SELECT CONCAT_WS ("", "Ivanov", "Ivan", "Ivanovich");
Result: "Ivanov Ivan Ivanovich"
SELECT CONCAT_WS (NULL, "Ivanov", "Ivan", "Ivanovich");
Result: NULL
SELECT CONCAT_WS ("", "Ivanov", NULL, "Ivan", "Ivanovich");
Result: "" Ivanov Ivan Ivanovich "

In case of concatenation of a large number of strings that need to be separated by a separator, the CONCAT_WS function is much more convenient than the CONCAT function.

Sometimes it is necessary to lengthen a string to a certain number of characters by repeating a character. This is also a kind of string concatenation. To do this, you can use the functions LPAD and RPAD... Functions have the following syntax:

string LPAD(str string, len integer, padstr string)
string RPAD(str string, len integer, padstr string)

The LPAD function returns str, left-padded with padstr to length len. The RPAD function does the same, except that the elongation occurs on the right side.

SELECT LPAD ("test", 10, ".");
Result: ...... test
SELECT RPAD ("test", 10, ".");
Result: test ......

In these functions, you need to pay attention to parameter len, which limits the number of characters to be displayed. Therefore, if the length of the string str is greater than the len parameter, then the string will be truncated:

SELECT LPAD ("test", 3, ".");
Result: tes

Determining string length in sql queries

To determine the number of characters in a string in the SQL language, the LENGTH function is responsible - the length of the string:

integer LENGTH(str string)

The function returns an integer equal to the number of characters in str.

SELECT LENGTH ("test");
Result: 4

In the case of using multibyte encodings, the LENGTH function does not return the correct result. For example, if the unicode encoding is set, then the request:

SELECT LENGTH ("test");

will return 8. Which, you can easily see, is twice the actual number of characters. In this case, you need to use the CHAR_LENGTH function:

integer CHAR_LENGTH(str string)

The function also returns line length str and supports multibyte characters.

For example:

SELECT CHAR_LENGTH ("test");
Result: 4

Search for a substring in a string using sql

There are several functions for calculating the position of a substring in a string in the sql language. The first one we'll look at is the POSITION function:

integerPOSITION(substr string IN str string)

Returns the position number of the first occurrence of substring substr in string str and returns 0 if the substring is not found. The POSITION function can handle multibyte characters.

SELECT POSITION ("cd" IN "abcdcde");
Result: 3
SELECT POSITION ("xy" IN "abcdcde");
Result: 0

The following LOCATE function allows you to start searching for a substring at a specific position:

integer LOCATE(substr string, str string, pos integer)

Returns the position of the first occurrence of substring substr in string str, starting at position pos. If the pos parameter is not specified, then the search is performed from the beginning of the string. If substr is not found, it returns 0. Supports multibyte characters.

SELECT LOCATE ("cd", "abcdcdde", 5);
Result: 5
SELECT LOCATE ("cd", "abcdcdde");
Result: 3

The INSTR function is analogous to the POSITION and LOCATE functions:

integer INSTR(str string, substr string)

Returns the position of the first occurrence of substring substr in string str as well as the above function. The only difference from the POSITION and LOCATE functions is that the arguments are swapped.

First, we will consider two functions LEFT and RIGHT at once, which are similar in their action:

string LEFT(str string, len integer)
string RIGHT(str string, len integer)

The LEFT function returns len of the first characters from string str, and the RIGHT function returns the same number of the last ones. Supports multibyte characters.

SELECT LEFT ("Moscow", 3);
Result: Mos
SELECT RIGHT ("Moscow", 3);
Result: kva

string SUBSTRING(str string, pos integer, len integer)
string MID(str string, pos integer, len integer)

The functions allow you to get a substring of str of length len characters from position pos. If the len parameter is not specified, then the entire substring starting from position pos is returned.

SELECT SUBSTRING ("Moscow is the capital of Russia", 4, 6);
Result: Moscow
SELECT SUBSTRING ("Moscow is the capital of Russia", 4);
Result: Moscow is the capital of Russia

I do not give examples with the MID function, because the results will be similar.

Interesting SUBSTRING_INDEX function:

string SUBSTRING_INDEX(str string, delim string, count integer)

The function returns a substring of str obtained by removing the characters after the delim at position count. The count parameter can be either positive or negative. If count is positive, then the position of the separator will be counted from the left and the characters to the right of the separator will be deleted. If count is negative, then the position of the separator is counted to the right and the characters to the left of the separator are removed. Perhaps the description turned out to be too confusing, but with examples it will become clearer.

SELECT SUBSTRING_INDEX ("www.mysql.ru", ".", 1);
Result: www

In this example, the function finds the first occurrence of the dot character in the string "www.mysql.ru" and removes all characters following it, including the separator itself.

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

Here, the function searches for the second occurrence of point, removes all characters to the right of it, and returns the resulting substring. And another example with a negative value for the count parameter:

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

In this example, the SUBSTRING_INDEX function searches for the second point by counting from the right, removes the characters to the left of it, and outputs the resulting substring.

Removing spaces from a string

There are three functions in SQL to remove extra spaces from the beginning and end of a string.

LTRIM function:

string LTRIM(str string)

Removes spaces from the beginning of str and returns the result.

RTRIM function:

string RTRIM(str string)

Also removes spaces from str, only from the end. Both functions support multibyte characters.

SELECT LTRIM ("text");
Result: "text"
SELECT RTRIM ("text");
Result: "text"

And the third TRIM function allows you to immediately remove spaces from the beginning and from the end of a string:

string TRIM([ string FROM] str string)

The str parameter is required, the rest of the parameters are optional. If only one parameter str is specified, then it returns the string str by removing spaces from the beginning and end of the string at the same time.

SELECT TRIM ("text");
Result: "text"

With the remstr parameter, you can specify characters or substrings to be removed from the beginning and end of the string. Using the control parameters BOTH, LEADING, TRAILING, you can specify where the characters will be removed from:

• BOTH - removes the remstr substring from the beginning and from the end of the string;
• LEADING - removes remstr from the beginning of the line;
• TRAILING - removes remstr from the end of the line.

SELECT TRIM (BOTH "and" FROM "text");
Result: "text"
SELECT TRIM (LEADING "and" FROM "textaa");
Result: "textaa"
SELECT TRIM (TRAILING "and" FROM "aaatext");
Result: "aaatext"

The SPACE function allows you to get a string consisting of a certain number of spaces:

string SPACE(n integer)

Returns a string consisting of n spaces.

The REPLACE function is needed for replaces given characters in a string:

string REPLACE(str string, from_str string, to_str string)

The function replaces all substrings from_str with to_str in str and returns the result. Supports multibyte characters.

SELECT REPLACE ("substring replacement", "substring", "text")
Result: "text replacement"

REPEAT function:

string REPEAT(str string, count integer)

The function returns a string that consists of count repetitions of str. Supports multibyte characters.

SELECT REPEAT ("w", 3);
Result: "www"

The REVERSE function reverses the line:

string REVERSE(str string)

Swaps all characters from last to first in str and returns the result. Supports multibyte characters.

SELECT REVERSE ("text");
Result: "tsket"

INSERT function to insert a substring into a string:

stringINSERT(str string, pos integer, len integer, newstr string)

Returns the string resulting from the insertion of the substring newstr into str at position pos. The len parameter specifies how many characters will be removed from str, starting at position pos. Supports multibyte characters.

SELECT INSERT ("text", 2, 5, "MySQL");
Result: "tMySQL"
"SELECT INSERT (" text ", 2, 0," MySQL ");
Result: "tMySQLext"
SELECT INSERT ("insert text", 2, 7, "MySQL");
Result: "SELECT INSERT (" insert text ", 2, 7," MySQL ");"

If you suddenly need to replace all capital letters in the text with uppercase letters, then you can use one of two functions:

string LCASE(str string) and string LOWER(str string)

Both functions replace uppercase letters in str and return the result. Both support multibyte characters.

SELCET LOWER ("ABVGDeZhZiKL");
Result: "abvgdezhzikl"

If, on the contrary, it is necessary to replace uppercase letters with capital letters, then one of the two functions can also be used:

string UCASE(str string) and string UPPER (str string)

The functions return the string str, replacing all uppercase characters with uppercase ones. Also supports multibyte characters.
Example:

SELECT UPPER ("Abvgdezhz");
Result: "ABVGDEZHZ"

There are slightly more string functions in the SQL language than discussed in this article. But since even most of the functions discussed here are rarely used, I will finish reviewing them. In the following articles, I will try to look at real practical examples of using SQL string functions. So don't forget to subscribe to blog updates. Until next time!

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

Result - 11. To find out what letters they are, we can use the CHAR function, which returns a character using a known ASCII code (from 0 to 255):

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

SELECT CHAR (ASCII ("a") + num-1) letter, ASCII ("a") + num - 1 FROM (SELECT 5 * 5 * (a-1) + 5 * (b-1) + c AS num FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x CROSS JOIN (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y CROSS JOIN ( SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z) x WHERE ASCII ("a") + num -1 BETWEEN ASCII ("a") AND ASCII ("z")

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

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

I suppose it won't be difficult to add this letter to the table if required.

Consider now the problem of finding the desired substring in a string expression. There are two functions that can be used for this - CHARINDEX and PATINDEX... They both return the starting position (position of the first character of the substring) of the substring in the string. The CHARINDEX function has the syntax:

CHARINDEX ( lookup_expression, string_expression[, start_position])

Here's an optional integer parameter start_position defines the position in the string expression from which the search is performed lookup_expression... If this parameter is omitted, the search is performed from the beginning string_expression... For example, the request

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

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

And here, for example, how you can find the names of ships that contain a sequence of three characters, the first and last of which is "e":

Steam room LEFT function RIGHT returns the specified number of characters to the right of a string expression:

RIGHT (<string expression>,<number of characters>)

For example, here's how you can identify ship names that start and end with the same letter:

Here we separate the class name and the ship name with a space. In addition, in order not to repeat the entire construction as an argument to a function, we use a subquery. The result will look like:

To eliminate this case, you can use another useful function LEN (<string expression>) which returns the number of characters in the string. Let's restrict ourselves to the case when the number of characters is more than one:

Function REPLICATE padding the constant "abcde" with five spaces to the right, which are not taken into account by the function LEN, - in both cases we get 5.
Function DATALENGTH returns the number of bytes in the representation of a variable and shows us the difference between CHAR and VARCHAR. DATALENGTH will give us 12 for CHAR and 10 for VARCHAR.
As expected, DATALENGTH returned the actual length of the variable for a VARCHAR variable. But why is the result equal to 12 for a variable of type CHAR? The point is that CHAR is a type fixed length... If the value of the variable is less than its length, and we declared the length as CHAR (12), then the value of the variable will be "aligned" to the required length by adding trailing spaces.

There are tasks on the site in which you want to order (find the maximum, etc.) in numerical order the values ​​presented in text format. For example, the seat number on the plane ("2d") or the CD speed ("24x"). The problem is that the text is sorted like this (ascending)

If you want to arrange the places in ascending order of rows, then the order should be as follows

If we only limit ourselves to this, then we get

It remains to sort

Today I propose to consider simple examples of use Transact-SQL string functions, and not just a description and examples of some functions, but their combination, i.e. how you can nest them into each other, since standard functions are not enough to implement many tasks and you have to use them together. And so I would like to show you a couple of simple examples of writing such queries.

We have already examined SQL string functions, but since the implementations of this language in different DBMSs are different, for example, some functions are not in Transact-SQL, but in PL / PGSql they are, and just last time we looked at string functions that can use in plpgsql and therefore today we will talk specifically about Transact-SQL.

How SUBSTRING, CHARINDEX and LEN can be combined

And so, for example, you need to search for a part of it in a string according to a certain criterion and cut it out, and not just search for a part of the same type, but dynamically, i.e. the search string will be different for each line. Examples will be written in Management Studio SQL Server 2008.

For this we will use the following functions:

• SUBSTRING(str, start, len) - this function cuts out a part of a string from another string. Has three parameters 1. This is the string itself; 2. The starting position from which to start cutting; 3. The number of characters, how many need to be cut.
• CHARINDEX(str1, str2) - searches str1 in str2 and returns the ordinal of the first character if such a string is found. Has a third optional parameter, with which you can specify which side to start the search from.
• LEN(str1) is the length of the string, i.e. Characters.

As you can see, here I used the declaration of variables, and you can substitute your own fields in the request instead of variables. Here's the code itself:

Declare @rezult as varchar (10) - source string declare @ str1 as varchar (100) - search string declare @ str2 as varchar (10) set @ str1 = "Test string string to search for another string in it" set @ str2 = "string" set @ rezult = substring (@ str1, CHARINDEX (@ str2, @ str1), LEN (@ str2)) select @rezult

The point here is that, using the len function, we find out how many characters need to be cut, and charindex sets the position from which to start cutting, and accordingly substring performs the selection itself in the string.

How LEFT, RIGHT and LEN can be combined

Let's say that you need to get the first few characters in a string or check these first characters in a string for the presence of something, for example, some number, and its length is naturally different (a test example, of course).

• Left(str, kol) - the function cuts out the specified number of characters from the left, has two parameters, the first is a string and the second, respectively, the number of characters;
• Right(str, kol) - the function cuts out the specified number of characters from the right, the parameters are the same.

Now we will use simple queries against the table

First, let's create a test_table:

CREATE TABLE (IDENTITY (1,1) NOT NULL, (18, 0) NULL, (50) NULL, CONSTRAINT PRIMARY KEY CLUSTERED (ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCPOWS = ON) ON) ON GO

Let's fill it with test data and write the following queries:

As you understand, the first query is just a selection of all rows (SQL Basics - select statement), and the second is a direct combination of our functions, here is the code:

Select * from test_table select number, left (text, LEN (number)) as str from test_table

And if these numbers were on the right, then we would use the function RIGHT.

Using Rtrim, Ltrim, Upper and Lower in combination

Assuming you have a line with spaces at the beginning and at the end, and you would, of course, get rid of them and, for example, make them, so that the first letter in this line becomes capitalized.

• Rtrim(str) - removes spaces from the right;
• Ltrim(str) - removes spaces from the left;
• Upper(str) - converts the string to upper case;
• Lower(str) - converts the string to lower case.

As you can see, for fixing we also used here Substring and Len... The meaning of the query is simple, we remove spaces from both the right and left, then convert the first character to uppercase by cutting it out, then we concatenate (operator +) this character with the rest of the string. Here is the code:

Declare @ str1 as varchar (100) set @ str1 = "test string with leading and trailing spaces" select @ str1 select upper (substring (rtrim (ltrim (@ str1)), 1,1)) + lower (substring ( rtrim (ltrim (@ str1)), 2, LEN (rtrim (ltrim (@ str1))) - 1))

For today, I think that's enough, and if you like programming in SQL, then on this site we have touched on this very interesting topic more than once, for example.