Sql add characters to the beginning of a string. SQL string functions - examples of use. Removing spaces from a string

Transact-SQL language 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 as the result of those calculations. Transact-SQL supports several common aggregate functions:

Calculates the arithmetic average of the data contained in a column. The values ​​on which the calculation is performed must be numeric.

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

Calculates the total sum of the values ​​in a column. The values ​​on which the calculation is performed must be numeric.

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 individual columns contain null values).

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 discussed in a future article.

Scalar functions

Transact-SQL scalar functions are used in creating scalar expressions. (A scalar function performs calculations on a single value or a list of values, while an aggregate function performs calculations on a group of values ​​over multiple rows.) Scalar functions can be broken down into the following categories:

numeric functions;

date functions;

string functions;

system functions;

metadata functions.

These types of functions are discussed in subsequent sections.

Numeric functions

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

Date functions

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

String functions

String functions manipulate the values ​​of columns, which are usually of a character data type. The supported string functions in Transact-SQL and their brief descriptions are given in the table below:

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. Using this identifier, the system can uniquely identify each database object.

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

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 their brief descriptions:

Last update: 07/29/2017

The following functions can be used to work with strings in T-SQL:

LEN: Returns the number of characters in a line. As a parameter, a string is passed to the function for which the length must be found:

SELECT LEN("Apple") -- 5

LTRIM: Removes leading spaces from a string. Takes the string as a parameter:

SELECT LTRIM(" Apple")

RTRIM: Removes trailing spaces from a string. Takes the string as a parameter:

SELECT RTRIM(" Apple ")

CHARINDEX: Returns the index at which the first occurrence of a substring in a string is found. A substring is passed as the first parameter, and the string in which to search is passed as the second:

SELECT CHARINDEX("pl", "Apple") -- 3

PATINDEX: Returns the index at which the first occurrence of a particular pattern is found in a string:

SELECT PATINDEX("%p_e%", "Apple") -- 3

LEFT: Cuts a specified number of characters from the beginning of a line. The first parameter of the function is the string, and the second is the number of characters that need to be cut from the beginning of the string:

SELECT LEFT("Apple", 3) -- App

RIGHT: Cuts a specified number of characters from the end of a string. The first parameter of the function is the string, and the second is the number of characters that need to be cut from the beginning of the string:

SELECT RIGHT("Apple", 3) -- ple

SUBSTRING: Cuts a substring of a specified length from a string, starting at a specific index. The first parameter of the function is the string, the second is the starting index for cutting, and the third parameter is the number of characters to cut:

SELECT SUBSTRING("Galaxy S8 Plus", 8, 2) -- S8

REPLACE: Replaces one substring with another within a string. The first parameter of the function is a string, the second is the substring to be replaced, and the third is the substring to be replaced with:

SELECT REPLACE("Galaxy S8 Plus", "S8 Plus", "Note 8") -- Galaxy Note 8

REVERSE : reverses the string:

SELECT REVERSE("123456789") -- 987654321

CONCAT : Concatenates two strings into one. As a parameter, it accepts 2 or more strings that need to be connected:

SELECT CONCAT("Tom", " ", "Smith") -- Tom Smith

LOWER : Converts the string to lower case:

SELECT LOWER("Apple") -- apple

UPPER : converts the string to uppercase

SELECT UPPER("Apple") -- APPLE

SPACE: returns a string that contains a specified number of spaces

For example, let's take the table:

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

And when retrieving data, we will use string functions:

SELECT UPPER(LEFT(Manufacturer,2)) AS Abbreviation, CONCAT(ProductName, " - ", Manufacturer) AS FullProdName FROM Products ORDER BY Abbreviation

Here is a complete list of string 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 argument.

Here, for example, is how you can determine how many different letters there are that start the names of ships in the Ships table:

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

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

and cuts the number of characters from the left specified by the second argument from the string that is the first argument. So,

Here's 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 number sequence, I refer you to the corresponding article.

As is known, codes of lowercase and capital letters are different. Therefore, to get the full set without rewriting the request, you just need to add a similar one to the above code:

I figure it wouldn't be too difficult to add this letter to the table if needed.

Let us now consider the task of determining where to find the desired substring in a string expression. Two functions can be used for this - CHARINDEX And PATINDEX. They both 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's an optional integer parameter start_position defines the position in a string expression from which the search is performed search_expression. If this parameter is omitted, the search is performed from the beginning string_expression. For example, 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 occurrence of the character "a" in the ship name "California"

Please note that when defining the second character in the function, the starting position is used, which is the position of the character next to 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 wildcard characters - % and _. In this case, the trailing characters "%" are required. For example, using this function in the first example would look like

The result of this query looks like this:

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

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

What if the string expression contains only one letter? The query will bring it up. You can easily verify this by writing

In this part we will talk about functions for working with text information that can be used in queries and program code in the PL/SQL language.

Function CONCAT(strl, str2)

This function concatenates the strings strl and str2. If one of the arguments is NULL, then it is treated as an empty string. If both arguments are NULL, then the function returns NULL. Example:

SELECT CONCAT("The priest had a dog") x1,
CONCATCTest" , NULL) x2,
CONCAT(NULL, "Test") x3,
CONCAT(NULL, NULL) x4
FROM dual

To concatenate strings, Oracle supports a special concatenation operator "||" that works similar to the CONCAT function, for example:

SELECT CONCAT("The priest "had a dog") x1, "The priest " || "had a dog" x2
FROM dual

The concatenation operator "||", which is equivalent to calling the CONCAT function, should not be confused with the "+" operator used in arithmetic operations. In Oracle these are different operators, but due to automatic type casting, subtle errors are possible, for example:

SELECT "5" + "3" x1
FROM dual

In this case, the numeric value 8 is returned rather than the text string "53". This is because when Oracle detects the arithmetic operator “+”, Oracle automatically tries to cast the arguments to type NUMBER.

Function LOWER(str)

The LOWER function converts all characters in str to lowercase. Example:

SELECT LOWER("TeXt DATA") X
FROM dual

FunctionUPPER(str)

The UPPER function converts all characters in the string str to uppercase. Example:

SELECT UPPER("TeXt DATA") X
FROM dual

INITCAP(str) function

Returns the string str with the first letters of all words converted to uppercase. The function is convenient for formatting the full name when building reports. Example:

SELECT INITCAPCIVANOV peter sidorovich") X
FROM dual

FunctionsLTRIM(str [,set])AndRTRIM(str [,set])

The LTRIM function removes all characters from the beginning of a string up to the first character that is not in the set character set. By default, set consists of a single space and may not be specified. The RTRIM function is similar to LTRIM, but removes characters starting from the end of the string. Let's look at a few examples:

SELECT LTRIM(" TeXt DATA") X1,
LTRIM(" _ # TeXt DATA", " #_") X2,
LTRIM(" 1234567890 TeXt DATA", " 1234567890") X3
FROM dual

Function REPLACE(str, search_str, [,replace_str])

The REPLACE function searches for a search_str pattern in the string str and replaces each occurrence found with replace_str. By default, replace_str is the empty string, so calling REPLACE with two arguments removes all occurrences found. The search for a substring is case sensitive. Example:

SELECT REPLACE("The priest had a dog", "dog", "cat") x1,
REPLACE("The priest had an evil dog", "evil") x2,
REPLACE("The priest had a dog", "Dog", "Cat") x3
FROM dual

Function TRANSLATE(str, from_mask, to_mask)

The TRANSLATE function parses the string str and replaces all characters appearing in the string from_mask with the corresponding characters from to_mask. For the function to work correctly, the from_mask and to_mask strings must be the same length, or the from_mask string must be longer than to_mask. If from_mask is longer than to_mask, and while processing the string str, characters are found that match one of the from_mask characters, and there is no match for them in to_mask, then such characters will be removed from the string str. If you pass from_mask or to_mask equal to NULL, the function will return NULL. The comparison is made case-sensitive.

SELECT TRANSLATE("Test 12345", "e2\"E!") x1,
TRANSLATE("Test 12345", "e234", "E") x2
FROM dual

This function is convenient for solving a number of practical problems related to character conversion or searching for prohibited characters. For example, you need to analyze a password and find out whether it contains at least one digit. The implementation of this check using TRANSLATE looks like this:

IF TRANSLATE(PassWd, "0123456789", "*") = PassWd THEN
ADD_ERR0R("Error - Password must contain at least one digit!");
RETURN 1;
ENDIF;

Another example: a number is being prepared for its conversion to NUMBER. It is necessary to replace the decimal separators "," and "." on "." and remove spaces. The implementation of this operation using TRANSLATE looks like this:

SELECT TRANSLATE("123 455.23", "., ", " . . ") x1,
TRANSLATE("-123 455.23", "., ", " . . ") x2
FROM dual

Function SUBSTR(str, m [,n])

The SUBSTR function returns a fragment of the string str, starting at character m, with a length of n characters. The length can be omitted - in this case, the string is returned from the character m to the end of the string str. Characters are numbered starting from 1. If you specify m = 0, then copying will still start from the first character. Specifying a negative value for m causes characters to be counted from the end of the string rather than from the beginning. Specifying values ​​of m that are greater in absolute value than the length of the string causes the function to return NULL.

SELECT SUBSTR("The priest had a dog", 13) x1,
SUBSTR("The priest had a dog", -6) x2,
SUBSTR("This is test text", 5, 8) x3,
SUBSTR("The priest had a dog", 150) x4
FROM dual

Function INSTR(str, search_str [,n[,m]])

The INSTR function returns the position of the first character m-ro of the string fragment str that matches search_str. The comparison is carried out from the nth character of the string str; the comparison is case-sensitive. By default, n = m = 1, that is, the search is carried out from the beginning of the line and the position of the first fragment found is returned. If the search is unsuccessful, the function returns 0.

SELECT INSTR("y butt was a dog", "dog") x1,
INSTR("y butt was a dog", "cat") x2,
INSTR("This is text to demonstrate text search", "text", 1, 2) x3,
INSTR(‘11111000000001", "1", 7) x4
FROM dual

This function, as well as all others in Oracle, is often allowed typical errors related to handling NULL values. If str=NULL, then the function will return NULL, not zero! This must be taken into account when constructing various conditions. For example, this fragment of a PL/SQL program does not take this feature into account:

IF INSTR(TXT_VAR,"*") = 0 THEN
...
ENDIF;

In this case, it would be correct to write like this:

IF NVL(INSTR(TXT_VAR, "*"), 0) = 0 THEN
...
ENDIF;

LENGTH (str) and LENGTHB (str) functions

The LENGTH(str) function returns the length of the string str in characters. For an empty string and a NULL value, the function returns NULL, so it is recommended to use NVL in conjunction with this function.

SELECT LENGTH("The priest had a dog") x1,
LENGTH("") x2,
LENGTH(NULL) x3,
NVL(LENGTH(""), 0) x4
FROM dual

The LENGTHB function is similar to the LENGTH function, but returns the length of the string in bytes.

ASCII(str) function

Returns the ASCII value of the first character of the string str when using ASCII character encoding, and the value of the first byte of a multibyte character when using multibyte character encoding. Example:

SELECT ASCII("Test") x1 FROM dual

Function CHR(n)

Returns a character by its code.

SELECT CHR(64) x1
FROM dual

Today I propose to look at simple examples of use Transact-SQL string functions, and not just a description and examples of some functions, but their combination, i.e. how can you nest them inside each other, since for the implementation of many tasks standard features sometimes it is not enough and you have to use them together. And that's why I'd like to show you a couple simple examples writing such requests.

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

How to combine SUBSTRING, CHARINDEX and LEN

And so, for example, you need to search for a part of 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 string. We will write examples in Management Studio SQL Server 2008.

To do this we will use the following functions:

• SUBSTRING(str, start, len) – this function cuts 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 you need to cut.
• CHARINDEX(str1, str2) - searches str1 in str2 and returns the serial number of the first character if such a string is found. It has a third optional parameter, with which you can specify which side to start the search from.
• LEN(str1) - string length, i.e. Characters.

As you can see, here I used variable declarations, 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 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 this: using the len function, we find out how many characters need to be cut, and charindex sets the position from which we need to start cutting, and accordingly substring performs the selection itself in the string.

How to combine LEFT, RIGHT and LEN

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 kind of number, and its length is naturally different (the example is, of course, a test one).

• Left(str, kol) – functions cut out the specified number of characters from the left, have two parameters, the first is the string and the second is the number of characters;
• Right(str, kol) - functions cut 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 table test_table:

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

As you understand, the first query is simply 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

Let's say you have a line with spaces at the beginning and at the end, and you would, of course, like to get rid of them and, for example, make the first letter in this line capitalized.

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

As you can see, to secure here we also used Substring And Len. The meaning of the request is simple, we remove spaces both on the right and on the left, then we convert the first character to uppercase by cutting it out, we then concatenate (the + operator) this character with the remaining string. Here's 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, For example.