home > Internet > Sql server string functions. SQL string functions

Sql server string functions. SQL string functions

Sql String Functions

This group of functions allows you to manipulate text. There are many string functions, we will consider the most common ones.

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, there were no extra spaces, so 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 .

As already mentioned, there are many more string functions, but even some of those considered here are used extremely rarely. Therefore, this will end their consideration and move on to more commonly used date and time functions.

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

Aggregate functions

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

AVG

Calculates the arithmetic mean of the data contained in a column. The values on which the calculation is performed 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 temporary (date/time).

SUM

Calculates the total sum of the values in a column. The values on which the calculation is performed 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 individual columns contain null values).

COUNT_BIG

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

Using normal aggregate functions in SELECT statements will be discussed in one of the following articles.

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 broken down into the following categories:

numerical 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. The 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, arc tangent of the value of n (for ATN2, the arc tangent of n/m is calculated). The input 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, calculating the cosine, sine, tangent, cotangent of the value n. The result is of data type FLOAT.
DEGREES, RADIANS	DEGREES(n), RADIANS(n)	The DEGREES function converts radians to degrees, RADIANS, respectively, 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 the value of n to the nearest p. When p is a positive number, the fractional part of n is rounded, and when negative, the integer part is rounded off. When using the optional argument t, the number n is not rounded, but truncated (that is, 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 nearest whole number.	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 (ie base e) of the number n, LOG10(n) - calculates the decimal (base 10) logarithm of the number n.
PI	PI()	Returns the value of π (3.1415).
POWER	POWER(x, y)	Calculates the x y value.
RAND	RAND()	Returns an arbitrary FLOAT number 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 the number n, SQUARE(n) - returns the square of the argument n.

Date functions

The date functions evaluate the corresponding parts of the date or time of an expression, or return the value of a time interval. The date functions supported by Transact-SQL and a brief description of them are listed 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 part of date 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 part of the date specified in the item parameter as a character string.	Returns January SELECT DATENAME(month, "01/01/2012") -- Returns Wednesday SELECT DATENAME(weekday, "01/02/2012")
DATEDIFF	DATEDIFF (item, dat1, dat2)	Computes 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 between dates) SELECT DATEDIFF(year, "01/01/1990", "01/01/2010") -- Returns 7305 (7305 days between dates) SELECT DATEDIFF(day, "01/01/1990", "01/01/2010") .2010")
DATEADD	DATEADD (item, n, date)	Adds nth quantity units specified in the item argument by the specified date date. (The value of the n argument can also be negative.)	Add 3 days to current date SELECT DATEADD(day, 3, GETDATE())

String Functions

String functions manipulate column values, which usually have character type data. The string functions supported by Transact-SQL and a brief description of them are listed 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("W") -- 1102
CHAR, NCHAR	CHAR(int), NCHAR(int)	Converts an ASCII code (or Unicode if NCHAR) to the corresponding character.	SELECT CHAR(87) -- "W" SELECT NCHAR(1102) -- "w"
CHARINDEX	CHARINDEX (str1, str2)	Returns the starting position of the occurrence of the substring str1 in the string str2. If the string str2 does not contain the substring str1, the value 0 is returned.	Returns 5 SELECT CHARINDEX ("morph", "polymorphism")
DIFFERENCE	DIFFERENCE(str1, str2)	Returns an integer from 0 to 4 that 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. With this method, similar-sounding strings can be determined. Only works for ASCII characters.	Returns 2 SELECT DIFFERENCE ("spelling", "telling")
LEFT, RIGHT	LEFT(str, length), RIGHT(str, length)	Returns the number of first characters of the string str, given by the length parameter for LEFT, and the last length characters of str for the RIGHT function.	DECLARE @str nvarchar(30) = "Sync"; -- Return "sync" SELECT LEFT(@str, 4) -- Return "zation" SELECT RIGHT(@str, 5)
LEN	LEN(str)	Returns the number of characters (not the number of bytes) of the string given 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 the string str to uppercase.	DECLARE @str nvarchar(30) = "Sync"; -- Return "SYNC" SELECT UPPER(@str) -- Return "sync" SELECT LOWER(@str)
LTRIM, RTRIM	LTRIM(str), RTRIM(str)	The LTRIM function removes leading spaces in the string str, RTRIM accordingly removes spaces at the end of the string.
QUOTENAME	QUOTENAME(char_string)	Returns a delimited Unicode string to convert the input string to a valid delimited identifier.	DECLARE @str nvarchar(30) = "Sync"; -- Returns "[Sync]" SELECT QUOTENAME(@str)
PATINDEX	PATINDEX(%p%,expr)	Returns the starting position of the first occurrence of the pattern p in the given expr, or zero if the given pattern is not found.	Returns 4 SELECT PATINDEX("%xro%", "Sync")
REPLACE	REPLACE(str1, str2, str3)	Replaces all occurrences of the substring str2 in the string str1 with the substring str3.	Returns "Desync" SELECT REPLACE("Sync", "Sync", "Desync")
REPLICATE	REPLICATE(str, i)	Repeats the string str i times.	Returns "aBaBaBaBaB" SELECT REPLICATE("aB", 5)
REVERSE	REVERSE(str)	Prints the string str in reverse order.	Returns "yaicazinorchnis" SELECT REVERSE("Sync")
SOUNDEX	SOUNDEX (str)	Returns a four-character soundex code used to determine the similarity of two strings. Only works for ASCII characters.
SPACE	SPACE (length)	Returns a string of spaces of the length specified by the length parameter. Similar to REPLICATE(" ", length).
STR	STR (f[, len[, d]])	Converts the given floating-point expression f to a string, where len is the length of the string, including decimal point, sign, digits, and spaces (default is 10), and d is the number of fractional digits to return.	Returns "3.14" SELECT STR (3.1415, 4, 2)
STUFF	STUFF (str1, a, length, str2)	Removes length characters from the string str1, starting at position a, and inserts the 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)	Extracts from the string str, starting at position a, a substring of length length.

System functions

System functions Transact-SQL languages provide a wealth of information about database objects. Most system functions use an internal numeric identifier (ID) that is assigned to each database object when it is created. With this identifier, the system can uniquely identify each database object.

AT following table some of the most important system functions are listed along with their 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 type (if possible). The w argument can be any valid expression.	Returns 3 SELECT CAST (3.1258 AS INT)
COALESCE	COALESCE (a1, a2)	Returns the first expression value in the list of expressions a1, a2, ... that is not null.
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. 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.
DATAENGTH	DATAENGTH (z)	Returns the number of bytes that the 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 complies with the ANSI SQL standard.
ISNULL	ISNULL (expr, value)	Returns the value of expr if it is not NULL; in otherwise value is returned.
ISNUMERIC	ISNUMERIC (expr)	Determines whether expr has a valid numeric type.
NEWID	NEWID()	Creates a unique ID consisting of a 16-byte binary string that is used to store 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 a default value for a column.)
NULLIF	NULLIF(expr1,expr2)	returns null, if the values of the expressions expr1 and expr2 are the same.	The query returns NULL for a project whose -- has Number = "p1" SELECT NULLIF(Number, "p1") FROM Project
SERVERPROPERTY	SERVERPROPERTY (propertyname)	Returns 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 ID is not specified, then 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 lists some of the most important functions metadata along with their 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 the id tab_id.	Returns the name of the column "LastName" SELECT COL_NAME (OBJECT_ID("Employee"), 3)
COLUMN PROPERTY	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 ID of the current database is returned.
DB_NAME	DB_NAME()	Returns the name of the database that has the id db_id. If identifier is not specified, then the name of the current database is returned.
INDEX_COL	INDEX_COL (table, i, no)	Returns the indexed column name of the table table. The column is indicated by the index ID i and the position no of the column in that index.
INDEXPROPERTY	INDEXPROPERTY (obj_id, index_name, property)	Returns the named index or statistic properties for the specified table ID, index or statistic name, and property name.
OBJECT_NAME	OBJECT_NAME (obj_id)	Returns the name of the database object with obj_id.	SELECT OBJECT_NAME(245575913);
OBJECT_ID	OBJECT_ID (obj_name)	Returns the obj_name object ID 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.

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 simple examples writing such requests.

You and I have already considered SQL string functions, but since the implementations of this language in different DBMS are different, for example, some functions are not in Transact-SQL, but they are in PL / PGSql, and just last time we considered string functions that can be use 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 look for a part of the same type, but dynamically, i.e. for each line, the search string will be different. We will write examples in Management Studio SQL Server 2008.

To do this, we will use the following functions:

SUBSTRING(str, start, len) - given function cuts part of a string from another string. It has three parameters 1. This is the string itself; 2. The starting position from which you want to start cutting; 3. The number of characters, how much to cut.
CHARINDEX(str1, str2) - searches for str1 in str2 and returns the ordinal number of the first character if such a string is found. It has a third optional parameter, with which you can specify from which side to start the search.
LEN(str1) - string length, 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 is the code itself:

Declare @result 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" set @str2 = "string" set @result=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 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 number, and its length is naturally different (naturally, the test example).

Left(str, kol) - the function cuts 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 requests to table

First, let's create the 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_LOCKS = ON, ALLOW_PAGE_LOCKS = 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 - the select statement), and the second is already 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 like to get rid of them, of course, and also, for example, make the first letter in this line capitalized.

Rtrim(str) - remove spaces on the right;
Ltrim(str) - remove 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 on both the right and left, then we bring the first character to upper case by cutting it out, then we concatenate (the + operator) that 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 enough, and if you like programming in SQL, then on this site we have repeatedly touched on this very interesting topic, for example.

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 converts the number 2 from decimal 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

Sometimes a 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 at 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.