New T-SQL string functions in Azure SQL Database

1 Mart 2017 Çarşamba

New T-SQL aggregate and string functions are available in Azure SQL database:

  • STRING_AGG () aggregate that concatenates values from a table by using the specified separator. An example of a query that returns all departments with a list of employee phone numbers separated with commas, and a list of email addresses separated with semicolons, is:

SELECT DepartmentName, STRING_AGG ( Phone, ',' ) as Phones, STRING_AGG ( EmailAddress, ';' ) WITHIN GROUP (ORDER BY EmailAddress) as Emails
FROM Employee
GROUP BY DepartmentName

Note that you can use an additional WITHIN GROUP clause to sort the values before concatenation. In this example, email addresses are sorted alphabetically.

  • TRIM() function that can remove any set of characters from both sides of the string. The following example returns descriptions of the articles where spaces are trimmed in first column; and dots, commas, and semicolons are trimmed in the second column: 

SELECT TRIM (Description), TRIM( '.,!' FROM Description)
FROM Article

  • CONCAT_WS() function that concatenates values by using the separator defined as the first parameter. The following query returns the name of the customer and the address, with address parts separated with commas:

SELECT Name, CONCAT_WS ( ',', AddressLine1, AddressLine2, Town, Region, Country, ZipCode) as FullAddress
FROM Customers

All new functions are also available in natively compiled stored procedures, functions, and triggers.

Ücretsiz hesap

$200 değerinde Azure kredisi elde edin ve popüler hizmetlerden 12 ay boyunca ücretsiz olarak yararlanın

Ücretsiz kullanmaya başlayın

Visual Studio

Aboneler yıllık $1800 tutarına kadar Azure hizmeti edinir

Şimdi etkinleştirin

Startup’lar

BizSpark programına katılıp ücretsiz Azure hizmetlerinden yararlanın

Daha fazla bilgi edinin