hash function. Another important advantage of using PGP is that PGP uses a so-called "hash function", which operates in such a way that in the event of any change in information, even by one bit, the result of the "hash function" will be completely

The uni() function Finding/replacing a character by its unicode number can also be done using the uni() function. An example of the uni() function: Boouni(107,32)Designer will find the word Book

Hash function However, the scheme described above has a number of significant drawbacks. It is extremely slow and produces too much data - at least twice the amount of original information. An improvement to such a scheme is the introduction to the transformation process

Method Concat Description of methodsMethods are given for the sequence sequence of T. function Concat(second: sequence of T): sequence of T; Joins two sequences by appending the second to the end of the first and returning the result

GROUP_CONCAT() in MySQL.
Today I want to talk about an interesting feature in Mysql.
As it turned out, very few programmers know it, I don’t know why few people use it, but I interviewed a dozen of my friends and less than half knew about GROUP_CONCAT.
This function works like this CONCAT_WS(Concatenates strings with a delimiter), only unlike CONCAT_WS able to combine the results of a selection from a table.

Syntax
GROUP_CONCAT( expr [,expr ...]
[,col_name ...]]
)

Let me give you a common example with pets.
For example, there is a table in which each person is assigned the animal that he has.

CREATE TABLE `test` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(250) NOT NULL, # is the person's name
`pet` varchar(250) NOT NULL, # this is a pet
PRIMARY KEY (`id`)
);

The data in the table is like this
masha - parrot
light - cat
masha - hamster
Julia - dog
light - dog
masha - fish

From this table it can be seen that
Masha has a parrot, a hamster and fish,
Sveta has a cat and a dog,
And Julia has only a dog.
So there is a task to display all the people from the table and so that next to each one it is shown what animals he has.

Let's make a request like this
SELECT `name`, GROUP_CONCAT(`pet`) as `pet`
FROM `test`
GROUP BY `name`

The result is like this
Masha - parrot, hamster, fish
lights - cat, dog
Julia - dog

It turned out the union of all the animals of each person.
GROUP BY `name` at the end, it groups identical names, one might say discards duplicates.

Of course, the example is not very vital, such redundant data is not often found, usually the names are stored in one table, the animals in the second, and the links in the third table.
That's just for such a case and it will be useful GROUP_CONCAT().
I will give an example with a nested query.

(select GROUP_CONCAT(`pet`) as `pet` from `test` where `name` = `name1`)as `pet`
FROM `test`

This example uses a nested select to create an animal combo box...
DISTINCT discards identical names. Field name we rename it so that it can be correctly addressed in the nested query.
This example is already more like an example from life.
Names are displayed, discarding identical ones, a list of animals is pulled out for each name.
Everything is very simple. You don't have to get every name and then pull the animals for every name, it's all done in one request.

And now more about this tricky GROUP_CONCAT.

Separator.
The default separator is comma.
If necessary, you can specify a different separator

SELECT DISTINCT `name` as `name1`,
(select GROUP_CONCAT(`pet` SEPARATOR "::") as `pet` from `test` where `name` = `name1`)as `pet`
FROM `test`

Now the animals will not be separated by a comma, but by a double colon ( :: )

And the rest of the possibilities of this function are clear from the described syntax (sorting and excluding identical records).

Several disappointments.

Limit 1024.
This function has a limit on the amount of output data.
The default is 1024 characters per concatenation - per output line.
If the size of the glued data is larger, then it will be truncated.
To expand the size you need to run the command SET group_concat_max_len =4096;
If you have privileges, then you will expand the amount of data received to 4096, and more.
But most often on regular hosting there are no such privileges.

Text only.
Next Feature GROUP_CONCAT it only works with strings.
If you want to stick together numbers, then you will not succeed, you need to convert the number to text.

Let's say you want to get not animals, but a list of IDs.
The usual option will not work, you need to convert the number to text, for example like this

SELECT DISTINCT `name` as `name1`,
(select CONVERT(GROUP_CONCAT(`id`) USING cp1251) from `test` where `name` = `name1`)as `pet_id`
FROM `test`

Here is an overview of an interesting feature GROUP_CONCAT().
It is best to use it for information, you should not rely on the fact that you will receive absolutely all the data, since there may be more data than set in the limit.
For example, you display a list of products on the page.
Each item has a price, availability, weight, and a brief description. And here you can, for example, show for each product in what colors this product is available.
Since a lot of products are displayed on this page, therefore, additional information about the product should be shown concisely, and here you can slip information in what colors the product is present. Display for example the 5 most popular colors of this product.
Obviously, if 20 products are displayed on the page, then you will never show information about colors larger than 1024 characters for each product, there is no point in reloading the page.
Here in such places it makes sense to use GROUP_CONCAT().

Comments

27.11.2008 Eugene
Not a bad chill

07.12.2008 Viola
A great opportunity. I knew about it, but did not know that there was a restriction. It's just not clear why converting to text? I got just a list of IDs and no conversion.

07.12.2008 Vadim
Viola, your IDs must have been stored in text fields.
If the field in the base has a numeric type, then this function will not display anything without conversion ... It must be converted to text.

07.12.2008 Viola
No, I had an ID field - int (11), auto-increment, primary key. Perhaps the fact is that after the selection, I do an implicit conversion to text - I cling the result to the string. But in the selection itself there is nothing of the kind and it returns the required result.

12/24/2008 Vadim
Well, it's hard to say what's the matter, perhaps this is due just to your implicit conversions...
But I myself checked on three different versions of mysql and it is necessary to convert to text, otherwise it does not give a value, but some information about the field.

02/24/2009 Sergio
Viola speaks the matter, I also output Int normally. Version 5.1

04/24/2009 Gumer
THANKS!!!

It was very necessary.

06/16/2009 Nigina
I have an error, although I copied everything correctly;)

10/18/2009 tyman8992
mysql 5.0.51b, InnoDB table - INT(11) conversion required, just checked.

21.10.2009 Artur
Thank you.
It turned out to display the result in a format similar to a pivot table in Excel.

12/21/2009 Semyon
Along the way, there is another feature.
The result of the union cannot be greater than 255. This is sad :(

12/21/2009 admin
255 is not enough, maybe 1024?

19.01.2010
m you can concatenate multiple fields and insert a delimiter
for example it works fine
select msisdn, group_concat(week,"=",charging order by week) from y2009 group by msisdn
380xxxxxxxxxx 40=8.41=4.44=8.45=8.46=4.47=1.50=8.51=9.52=4.53=1

03/09/2010 Dmitry
But I can't get the query

SELECT product.id,title, catid, GROUP_CONCAT(`idSpecific`) AS `specific`
FROM `product`
LEFT JOIN `specification_items` ON ​​product.id = idProduct
WHERE catid =46 GROUP BY title

Result specific

03/09/2010 admin
this function only works with strings.
and in your case most likely there is a processing of number.
you need to convert the data like this
CONVERT(GROUP_CONCAT(`idSpecific`) USING cp1251)

03/24/2010 Dmitry
Thank you very much converted :)

25.03.2010 AJ
Is there any way to avoid nested selects? if you need to display several hundred (thousand) records in a query, it will be the same number of nested queries.

26.03.2010 Victor
But isn't it possible to avoid nested selects through JOIN?
although join will probably not help here, since many rows are selected in one query ...
probably without nested selekta it not to make.

05/24/2010 USVR
Nice feature! Helped to avoid the invention of the bicycle and its own serialization.

To AJ, And here, whatever one may say, a subquery is required. The only question is who will perform this subrequest - the client or the server. Of course, using the term "subquery" is not entirely correct, I'm not sure, but most likely on the server there will be only one selection of the subquery at once for the entire external request, which is impossible to implement on the client.
What a pity that there is a limitation. It is generally meaningless and justified only as a measure of protection from something (Buffer overflow?).

05/24/2010 USVR
Probably expressed it wrong
Optimization is possible on the server (this is the essence of all aggregate functions).
I don't know exactly how this function is optimized, but it obviously requires less resources than data processing in the client.

The Concat (Str1,Str2,...,StrN) function concatenates (or concatenates) the strings Str1,Str2,...,StrN in the order in which they are specified in the parameter list. The sum of the characters of all concatenated strings must not exceed 255.

Program DemoFunctionConcat; VarWord:string; Word1, Word2: string; Begin Word1:= "companies"; Word2:="Microsoft"; Word:= Concat("Computers ",Word1,Word2); writeln(Word); (text "Microsoft Computers" is displayed) End.

Tasks for independent solution

A 1st declension noun ending in “a” is given. Check if the input is correct. Print this word in all cases. Apply subroutines.

Check the spelling of "cha" and "cha" in the text. Print the number of errors made and the line corrected. Apply subroutines.

Write an algorithm that counts the number of times a given word y occurs in a given word x. If the word y is longer than x, then the result must be zero. Apply subroutines.

Find out how many times each letter of the alphabet appears in the given text. Apply subroutines.

Write line a in reverse order to line b. Count how many identical letters are in the same places in these lines. Apply subroutines.

The last name, first name and patronymic of the student are given, separated by a space. Print his last name and initials. Apply subroutines.

Check the spelling of "zhi" and "shi" in the text. Print the number of errors made and the line corrected. Apply subroutines.

Request a custom offer and a name. Look for spelling mistakes and correct them. Print the number of errors made and the line corrected. Apply subroutines.

Count the number of digits in the given character string. Apply subroutines.

Count the number of letters in the entered character string. Apply subroutines.

Query multiple characters and a string to find out how many of the given characters occur in a string. Apply subroutines.

Standard procedures for working with strings (delete, insert, str, val).

Procedure Insert

The Insert procedure inserts another string into the source string, starting at the specified position. The Insert (Word1,Word2,5) statement specifies that the string Word1 should be inserted into the string Word2, starting at the 5th position.

Delete Procedure

The Delete procedure removes a fragment of a certain length from the source string, starting at the specified position. Thus, the Delete(Word1,2,3) operator removes a three-character fragment from the specified string, starting from the second.

Procedure Str

General view of Str(Chislo,Stroka)

The Str procedure converts the numeric value of the Chislo variable to the string variable Stroka. The first parameter can be followed by a format similar to the output format.

Program DemoProcedureStr; VarWord:string; number: integer; BeginNumber:= 1560; Str(Number:8, Word); writeln(Word); (the string " 1500" is displayed) End.

ProcedureVal

General form Val(Stroka,Chislo,Code)

The Val procedure converts the value of a Stroka to an integer or real value and places the result in Chislo. The value of the string variable Stroka must not contain spaces at the beginning and at the end. Code is an integer variable. If no error is found during the conversion operation, the value of Code is zero; if an error is found, Code will contain the position number of the first error character, and the value of Chislo will be undefined.

Program DemoProcedureVal; VarWord:string; Number, Code: integer; Begin writeln("Enter a string of numbers"); readln(Word); Val(Word, Number, Code); (convert string to number) if Code<>0 then writeln("Error! Position ",Code," is not a number!",); end.

Allows you to join strings. The function call looks like this:

concat(s 1 ,s 2 ,…,s n)

Example 6.9. A program to join two strings.

program prog6_9;

uses wincrt;

s:=concat(s1,s2);

insert procedure

Inserts a string within a string. The procedure call looks like this:

insert(st1,st2,n)

where st1 is the string to be inserted

st2 - the line into which the line st1 is inserted

n is the position from which the insertion starts.

Example 6.10. A program to insert a string into a string.

program Prog6_10;

uses wincrt;

insert(st1,st2,n);

Procedure Str

Allows you to convert numbers to a string. The procedure call looks like this:

str( Number,st);

where st is a string constant or variable containing an image of a number;

Number - variable, the numeric value is converted to a string and assigned to the variable st;

Example 6.11. A program to convert a number to a string.

program Prog6_11;

uses WinCrt;

readln(n); (Number input)

str(n,st); (number conversion n string)

writeln("number image =",st);

Control questions

1. What is a string?

2. What identifier defines string type data?

3. What is the maximum possible string length? How to determine the current

string length?

4. What expressions are called string?

5. What operations are allowed on string data?

6. How are strings compared?

7. What are the requirements for writing expressions with operands

string and literal type?

8. How can you access individual characters in a string?

9. Purpose of special procedures and data processing functions

string type. Give examples. \

Working with databases is constantly associated with obtaining the results of queries. And in some cases, this information needs to be displayed in a certain way or combined with other data. To solve this problem, there is a SQL function - CONCAT.

What does the CONCAT function do?

When performing some work with, there is a need to connect rows with additional data or with each other. To solve this problem, there is a SQL function - CONCAT. When using it, two or more lines will be combined into one. In this case, the string concatenation operation will be correctly performed both when working with strings and with numerical variables.

In some databases, SQL CONCAT has several flavors. So, in MySQL it has its analogue - CONCAT_WS. The difference between the functions is not significant: in the case of the first one, when combined with an argument whose value is NULL, the result of the concatenation will be NULL, and when using the second variant of the union, the zero operand will simply be skipped.

Function Syntax

When using the CONCAT function, the SQL syntax obliges the programmer to use the arguments in the order in which the operands are to be concatenated. The syntax of the string concatenation operation itself is quite simple: after the CONCAT keyword, all the necessary arguments or strings are specified in brackets separated by commas, and after the closing bracket, if necessary, the AS keyword and the name of the variable where the result will be written are indicated. The CONCAT function template looks like this:

CONCAT (line1, line2 [, line3, ...]) .

It is worth noting that both an operand of a numerical and string value, and some function that returns a result can be used as function arguments. For example, the SQL CONCAT operation itself. Thus, the operation supports the nesting principle.

How to do without using CONCAT?

In the case when there is a need to avoid using the operation, or the programmer is not aware of the CONCAT function, SQL offers another option for implementing string concatenation. To do this, you need to use the "+" operator, but this approach has a number of features that must be taken into account when working with it.

When using the "+" function, if the operands are numeric values, the result of the operation will be an ordinary addition of numbers, and if necessary, combine a number with a string without explicit conversion, the program will generate an error. So, when the following query is executed, the result will be the value of the number "6":

Otherwise, both methods are identical and return the same result.