All of us are familiar with Common Table Expression (CTE) because of its frequent use. We use it in our day-to-day operation quite frequently. Recently, I came across a case where I had to use CTE in a user defined function.
Let me create a sample user defined function (UDF) in Northwind database with the help of CTE to use in my demonstration as shown below.
USE Northwind
GO
CREATE FUNCTION [dbo].[udf_Most_Expensive_Product]
(
@SupplierID INT
)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @ProductName NVARCHAR(50)
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS SNo
, ProductName
, UnitPrice FROM Products
WHERE SupplierID = @SupplierID
)
SELECT
@ProductName = ProductName
FROM CTE
WHERE SNo = 1
RETURN @ProductName;
END
GO
Let me call the above created user defined function(UDF) and see the results.
USE Northwind
GO
SELECT [dbo].[udf_Most_Expensive_Product](2) AS [ProductName];
GO
--OUTPUT
Conclusion:
It is quite simple to use Common Table Expression (CTE) in user defined function (UDF).
In SQL Server 2022, an enhancement came in RTRIM() function, which was a long awaited functionality. This enhancement helps us to remove last character from the string. I have written an article about it in detail.
Recently, I was using RTRIM() function and came across an error as mentioned below.
Error:
Invalid length parameter passed to the rtrim function.
Sample:
USE tempdb
GO
CREATE TABLE #Employees
(
EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(250),
EmailAddress VARCHAR(MAX)
);
GO
INSERT INTO #Employees
VALUES ('Alex','alex114@gmail.com;')
, ('Sarina','sarina152@gmail.com;')
, ('Sophia','sophiaa123@gmail.com;');
GO
SELECT * FROM #Employees;
GO
Example:
In the given below example, I have written a simple script using RTRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.
USE tempdb
GO
DECLARE @separator VARCHAR(MAX);
SET @separator = ';';
SELECT EmployeeID
, EmployeeName
, EmailAddress
, RTRIM(EmailAddress, @separator) AS EmailAfterRTrim
FROM #Employees;
GO
Solution:
The solution to this problem is that while declaring datatype and length of a separator variable, we should never use MAX as its length since it does not support. We should always give length in numbers as shown below.
USE tempdb
GO
DECLARE @separator VARCHAR(1);
SET @separator = ';';
SELECT EmployeeID
, EmployeeName
, EmailAddress
, RTRIM(EmailAddress, @separator) AS EmailAfterRTrim
FROM #Employees;
GO
Conclusion:
Whenever you use RTRIM() function by using a separator variable, you should always define the length of the variable. Please don’t use MAX as a length to avoid such error.
In SQL Server 2022, an enhancement came in LTRIM() function, which was a long awaited enhancement. This enhancement helps us to remove first character from the string. I have written an article about it in detail.
Recently, I was using LTRIM() function and came across an error as mentioned below.
Error:
Invalid length parameter passed to the ltrim function.
Sample:
USE tempdb
GO
CREATE TABLE #Employees
(
EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(250),
EmailAddress VARCHAR(MAX)
);
GO
INSERT INTO #Employees
VALUES ('Alex','alex114@gmail.com;')
, ('Sarina','sarina152@gmail.com;')
, ('Sophia','sophiaa123@gmail.com;');
GO
SELECT * FROM #Employees;
GO
Example:
In the given below example, I have written a simple script using LTRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.
USE tempdb
GO
DECLARE @separator VARCHAR(MAX);
SET @separator = ';';
SELECT EmployeeID
, EmployeeName
, EmailAddress
, LTRIM(EmailAddress, @separator) AS EmailAfterLTrim
FROM #Employees;
GO
Solution:
The solution to this problem is that while declaring datatype and length of a separator variable, we should never use MAX as its length since it does not support. We should always give length in numbers as shown below.
USE tempdb
GO
DECLARE @separator VARCHAR(1);
SET @separator = ';';
SELECT EmployeeID
, EmployeeName
, EmailAddress
, LTRIM(EmailAddress, @separator) AS EmailAfterLTrim
FROM #Employees;
GO
Conclusion:
Whenever you use LTRIM() function by using a separator variable, you should always define the length of the variable. Please don’t use MAX as a length to avoid such error.
In SQL Server 2022, an enhancement came in TRIM() function which was a long awaited functionality. This enhancement helps us in removing first or last character from the string. I have written an article about it in detail.
Recently, I was using TRIM() function and came across an error as mentioned below.
Error:
Invalid length parameter passed to the trim function.
Sample:
USE tempdb
GO
CREATE TABLE #Employees
(
EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(250),
EmailAddress VARCHAR(MAX)
);
GO
INSERT INTO #Employees
VALUES ('Alex','alex114@gmail.com;')
, ('Sarina','sarina152@gmail.com;')
, ('Sophia','sophiaa123@gmail.com;');
GO
SELECT * FROM #Employees;
GO
Example:
In the given below example, I have written a simple script using TRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.
DECLARE @separator VARCHAR(MAX);
SET @separator = ';';
SELECT EmployeeID
, EmployeeName
, EmailAddress
, TRIM(@separator FROM EmailAddress) AS EmailAfterTrim
FROM #Employees;
GO
Solution:
The solution to this problem is that while declaring datatype and length of a separator variable, we should never use MAX as its length since it does not support. We should always give length in numbers as shown below.
DECLARE @separator VARCHAR(1);
SET @separator = ';';
SELECT EmployeeID
, EmployeeName
, EmailAddress
, TRIM(@separator FROM EmailAddress) AS EmailAfterTrim
FROM #Employees;
GO
Conclusion:
Whenever you use TRIM() function by using a separator variable, you should always define the length of the variable. Please don’t use MAX as a length in order to avoid such error.
SQL Server 2022brought some exciting features which will help us in optimising the SQL scripts. IS [NOT] DISTINCT FROM is one of the new features. It helps us in code optimisation and reduce the code complexity.
Compatibility Level:
Your database compatibility level MUST be 160 or higher to use this new feature.
ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160
Sample:
USE tempdb
GO
CREATE TABLE [#Customer](
[CustomerID] INT IDENTITY(1,1),
[CustomerName] VARCHAR(100),
[PhoneNumber] VARCHAR(100),
[AlternatePhoneNumber] VARCHAR(100));
GO
INSERT INTO [#Customer]
([CustomerName],[PhoneNumber],[AlternatePhoneNumber])
VALUES
('Maria','+92-300074321','+92-300074321'),
('Trujillo',null,null),
('Antonio','+96-562108460',null),
('Thomas',null,'+96-0515552282'),
('Christina','+92-059675428','+92-05676753425');
GO
SELECT * FROM [#Customer];
GO
--OUTPUT
Example 1:
IS DISTINCT FROM
Old Approach:(Using Complex WHERE Clause)
Whenever we need to search some records having NULL, the search criteria will look like this.
USE tempdb
GO
SELECT [CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
FROM [#Customer]
WHERE
(([PhoneNumber] <> [AlternatePhoneNumber]
OR [PhoneNumber] IS NULL
OR [AlternatePhoneNumber] IS NULL)
AND NOT ([PhoneNumber] IS NULL
AND [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT
New Approach:(Using IS DISTINCT FROM)
The complexity and length of where clause has been reduced by the new enhancement which comes with the name IS DISTINCT FROM which takes all unique records of two columns as well as return single null values and drop where both columns are null as shown below:
USE tempdb
GO
SELECT [CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
FROM [#Customer]
WHERE [PhoneNumber] IS DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT
Example 2:
IS NOT DISTINCT FROM
Old Approach:(Using Complex WHERE Clause)
In old methodology if we had to take same values from two columns and also where both columns have null values, then the query would be as shown below:
USE tempdb
GO
SELECT [CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
FROM [#Customer]
WHERE
(NOT ([PhoneNumber] <> [AlternatePhoneNumber]
OR [PhoneNumber] IS NULL
OR [AlternatePhoneNumber] IS NULL)
OR ([PhoneNumber] IS NULL
AND [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT
New Approach:(Using IS NOT DISTINCT FROM)
In new Approach, the complexity has been reduced to just single clause named IS [NOT] DISTINCT FROM & rest all conditions are applied by default as shown in the picture below:
USE tempdb
GO
SELECT [CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
FROM [#Customer]
WHERE [PhoneNumber] IS NOT DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT
Example 3:
IS DISTINCT FROM
Old Approach:(Using Complex HAVING Clause)
In old methodology if we had to take unique values using HAVING Clause from two columns and also single null values and drop where both columns are null, then the query would be as shown below:
USE tempdb
GO
SELECT [CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
FROM [#Customer]
GROUP BY
[CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
HAVING
(([PhoneNumber] <> [AlternatePhoneNumber]
OR [PhoneNumber] IS NULL
OR [AlternatePhoneNumber] IS NULL)
AND NOT ([PhoneNumber] IS NULL
AND [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT
New Approach:(Using IS DISTINCT FROM)
The complexity is being reduced by New Approach where we need to write only one clause and rest is handled by itself as shown in the example below.
USE tempdb
GO
SELECT [CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
FROM [#Customer]
GROUP BY
[CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
HAVING
[PhoneNumber] IS DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT
Example 4:
IS NOT DISTINCT FROM
Old Approach:(Using Complex HAVING Clause):
In old methodology if we had to take same values using HAVING from two columns and also where both columns have null values then the query would be as shown below.
USE tempdb
GO
SELECT [CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
FROM [#Customer]
GROUP BY
[CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
HAVING
(NOT ([PhoneNumber] [AlternatePhoneNumber]
OR [PhoneNumber] IS NULL
OR [AlternatePhoneNumber] IS NULL)
OR ([PhoneNumber] IS NULL
AND [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT
New Approach:(Using IS NOT DISTINCT FROM)
The complexity is being reduced by New Approach where we need to write only one clause and rest is handled by itself as shown in the example below.
USE tempdb
GO
SELECT [CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
FROM [#Customer]
GROUP BY
[CustomerID]
, [CustomerName]
, [PhoneNumber]
, [AlternatePhoneNumber]
HAVING [PhoneNumber] IS DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT
Conclusion:
The IS [NOT] DISTINCT Clause is a life saver where writing a query with multiple conditions just reduced to a single clause. It also reduces the complexity of the query. Do let me know if you used IS [NOT] DISTINCT Clause and found it useful.
In SQL Server 2022, a new enhancement was introduced in dynamic data masking known as Granular Permissions which was a long-awaited enhancement. This new enhancement gives us control to provide unmasking permission at lowest level (table’s column) which we could not achieve in the earlier version of SQL Server. In other words, we can give permissions to unmask columns, tables, schemas & databases to different users based on their accessibility levels.
Let me create a sample to demonstrate the functionality of Dynamic data masking granular permissions in which, I will create a Sample database, two schemas and each schema will have one table as shown below.
Sample:
--Create Sample Database
CREATE DATABASE SampleDB;
GO
USE SampleDB
GO
--Create HumanResources Schema
CREATE SCHEMA HumanResources;
GO
--Create Accounts Schema
CREATE SCHEMA Accounts;
GO
--Create Employee Table
CREATE TABLE HumanResources.Employee
(
EmployeeID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
EmployeeName VARCHAR(250),
Birthdate DATE MASKED WITH (FUNCTION = 'default()') NULL,
Salary DECIMAL(10,2) MASKED WITH (FUNCTION = 'default()') NULL
);
GO
--Create BankAccount Table
CREATE TABLE Accounts.BankAccount
(
AccountID INT IDENTITY(1,1) PRIMARY KEY,
EmployeeID INT NOT NULL,
AccountTitle VARCHAR(250) MASKED WITH (FUNCTION = 'default()') NULL,
AccountNumber VARCHAR(250) MASKED WITH (FUNCTION = 'partial(3,"xxxxxxxxx", 4)') NULL
);
GO
--Insert few records into Employee Table
INSERT INTO Humanresources.Employee(EmployeeName,Birthdate,Salary)
VALUES ('Nancy','1988-12-08','200000'),
('Andrew','1994-07-16','120000'),
('Janet','1994-07-16','75000');
GO
--Insert few records into BankAccount table
INSERT INTO Accounts.BankAccount(EmployeeID,AccountTitle,AccountNumber)
VALUES(1,'Nancy','9922-0105664197'),
(2,'Andrew','0010-0107605278'),
(3,'Janet','4010-3568743987');
GO
Configuration:
Step 1:
Let’s create few users, which will be used to provide relevant permissions to unmask data as shown below.
USE SampleDB
GO
--Create User HRAdministrator
CREATE USER HRAdministrator WITHOUT LOGIN;
GO
--Create User PayrollAdministrator
CREATE USER PayrollAdministrator WITHOUT LOGIN;
GO
--Create User Accountant
CREATE USER Accountant WITHOUT LOGIN;
GO
--Create User HRManager
CREATE USER HRManager WITHOUT LOGIN;
GO
Step 2:
Let’s add the db_datareader role in each user created above.
USE SampleDB
GO
--Grant db_datareader role to HRAdministrator
ALTER ROLE db_datareader ADD MEMBER HRAdministrator;
GO
--Grant db_datareader role to PayrollAdministrator
ALTER ROLE db_datareader ADD MEMBER PayrollAdministrator ;
GO
--Grant db_datareader role to Accountant
ALTER ROLE db_datareader ADD MEMBER Accountant;
GO
--Grant db_datareader role to HRManager
ALTER ROLE db_datareader ADD MEMBER HRManager;
GO
Step 3:
Let’s provide UNMASK permissions to above created users. The details of the access are as shown below:
HRAdministrator: can view Birthdate column Data only in Employee table.
PayrollAdministrator: can view Salary column Data only in Employee table .
Accountant : can view the entire tables data in Account schema only.
HRManager : can view the entire data in the SampleDB database.
USE SampleDB
GO
--Grant Birthday column unmask permission to HRAdministrator;
GRANT UNMASK ON Humanresources.Employee(Birthdate) TO HRAdministrator;
GO
--Grant salary column unmask permission to PayrollAdministrator
GRANT UNMASK ON Humanresources.Employee(Salary) TO PayrollAdministrator;
GO
--Grant Accounts schema unmask permission to Accountant
GRANT UNMASK ON SCHEMA::Accounts TO Accountant;
GO
--Grant entire database unmask permission to HRManager
GRANT UNMASK TO HRManager;
GO
Testing:
HRAdministrator
Let’s access the Employee table under the context of HRAdministrator user.
USE SampleDB
GO
EXECUTE AS USER='HRAdministrator';
SELECT EmployeeID
, EmployeeName
, Birthdate
, Salary
FROM Humanresources.Employee;
REVERT;
GO
--OUTPUT
As we can see above, the HRAdministrator can view the Birthdate column data in Employee table but cannot view Salary column data in Employee table.
Let’s access the BankAccount table under the context of HRAdministrator user.
USE SampleDB
GO
EXECUTE AS USER='HRAdministrator';
SELECT EmployeeID
, AccountTitle
, AccountNumber
FROM Accounts.BankAccount;
REVERT;
GO
--OUTPUT
As we can see above, the HRAdministrator can’t view the data of BankAccount Table since unmask permission is not given.
PayrollAdministrator
Let’s access Employee table under the context of PayrollAdministrator user.
USE SampleDB
GO
EXECUTE AS USER='PayrollAdministrator';
SELECT EmployeeID
, EmployeeName
, Birthdate
, Salary
FROM Humanresources.Employee;
REVERT;
GO
--OUTPUT
As we can see above, the PayrollAdministrator can view the Salary column data in Employee table but cannot view Birthdate column data in Employee table.
Let’s access the BankAccount table under the context of PayrollAdministrator user.
USE SampleDB
GO
EXECUTE AS USER='PayrollAdministrator';
SELECT EmployeeID
, AccountTitle
, AccountNumber
FROM Accounts.BankAccount;
REVERT;
GO
--OUTPUT
As we can see above, the PayrollAdministrator can’t view the data of BankAccount Table since unmask permission is not given.
Let’s access the Employee table under the context of Accountant user.
USE SampleDB
GO
EXECUTE AS USER='Accountant';
SELECT EmployeeID
, EmployeeName
, Birthdate
, Salary
FROM Humanresources.Employee;
REVERT;
GO
--OUTPUT
As we can see above, the Accountant cannot view the Salary, Birthdate column data in Employee table since unmask permission is not given.
Let’s access the BankAccount table which is in Accounts schema under the context of Accountant user.
USE SampleDB
GO
EXECUTE AS USER='Accountant';
SELECT EmployeeID
, AccountTitle
, AccountNumber
FROM Accounts.BankAccount;
REVERT;
GO
--OUTPUT
As we can see above, the Accountant can view the data of BankAccount Table.
Let’s access the Employee table which is in HumanResources schema under the context of HRManager user.
USE SampleDB
GO
EXECUTE AS USER='HRManager';
SELECT EmployeeID
, EmployeeName
, Birthdate
, Salary
FROM Humanresources.Employee;
REVERT;
GO
--OUTPUT
As we can see above, the HRManager can view the data of Employee Table.
Let’s access the BankAccount table which is in Accounts schema under the context of HRManager user.
USE SampleDB
GO
EXECUTE AS USER='HRManager';
SELECT EmployeeID
, AccountTitle
, AccountNumber
FROM Accounts.BankAccount;
REVERT;
GO
--OUTPUT
As we can see above, the HRManager can view the data of BankAccount Table.
Conclusion:
Granular Permission in dynamic data masking is a very handy enhancement, it gives us the control to unmask column, table, schema, database data to different users. Do let me know if you use it and found it helpful.
Removing the last character in a string has never been easier before SQL Server 2022 version. I had written a detailed articleback in 2013 regarding this issue, where I used, STUFF(), LEN() functions etc.
In SQL Server 2022, a new enhancement came in TRIM() & RTRIM() function, where we can define any characters to be removed from the String. I had written a detailed article about it earlier. By using above mentioned functions, we can simply remove the last character from the string.
Compatibility Level:
Database compatibility level MUST be 160 or higher to use New Approaches. Given below is the query to change your database compatibility level.
ALTER DATABASE AdventureWorks
SET COMPATIBILITY_LEVEL = 160
Let me demonstrate, how to remove last character from string in the following examples.
Example 1:
Old Approach:
In the old approach, we have to use multiple functions (STUFF(), LEN()) in order to remove the last character from string as shown below.
DECLARE @String as VARCHAR(50);
SET @String = 'KenSanchez@gmail.com;';
SELECT @String AS String
, STUFF(@String,LEN(@String), 1, '') AS LastCharacterRemoved;
GO
--OUTPUT
New Approach:
Using TRIM() function :
In the new approach, its quite simple, we can remove the last character from string by using TRIM() function as shown below.
DECLARE @String as VARCHAR(50);
SET @String = 'KenSanchez@gmail.com;';
SELECT @String AS String
, TRIM(TRAILING';' FROM @String) AS LastCharacterRemoved;
GO
--OUTPUT
Using RTRIM() function:
In the new approach, we can remove the last character from string by using RTRIM() function as shown below.
DECLARE @String as VARCHAR(50);
SET @String = 'KenSanchez@gmail.com;';
SELECT @String AS String
, RTRIM(@String,';') AS LastCharacterRemoved;
GO
--OUTPUT
Example 2:
Old Approach:
In the old approach, we had to use multiple functions (STUFF(), LEN()) in order to remove the last character from string as shown below.
USE Northwind
GO
SELECT EmployeeID
, FirstName
, LastName
, Title
, TitleOfCourtesy
, STUFF(TitleOfCourtesy
, LEN(TitleOfCourtesy), 1, '') AS LastCharacterRemoved
FROM Employees;
GO
--OUTPUT
New Approach:
Using TRIM() function:
In the new approach, its quite simple, we can remove the last character from string by using TRIM() function as shown below.
USE Northwind
GO
SELECT EmployeeID
, FirstName
, LastName
, Title
, TitleOfCourtesy
, TRIM(TRAILING '.' FROM TitleOfCourtesy) AS LastCharacterRemoved
FROM Employees;
GO
--OUTPUT
Using RTRIM() function:
In the new approach, we can remove the last character from string by using RTRIM() function as shown below.
USE Northwind
GO
SELECT EmployeeID
, FirstName
, LastName
, Title
, TitleOfCourtesy
, RTRIM(TitleOfCourtesy,'.') AS LastCharacterRemoved
FROM Employees;
GO
--OUTPUT
Conclusion:
In SQL Server 2022 or above, removing the last character from the string is quite easy, since there are multiple functions available to achieve it as shown above. Do let me know which approach are you using and how did you find it.
‘Filtration’ is one of the greatest features available in Azure Data Studio. You can filter data same like excel. I have written an article about it in detail. Recently, while using filter option in Azure Data Studio, I came across an error as shown below.
Let me demonstrate why we came across this error and how we can solve this error. The reason behind this error is that the maximum number of rows allowed to do filtering and sorting has exceeded, so we need to increase the threshold in order to filter table which can be done by the following steps.
Step 1:
In Azure Data Studio, click on the Manage option in the left bottom of the window as shown below.
Step 2:
In Manage, click on Settings option as shown below.
Step 3:
It will open Settings window, please select Data menu. In Data menu, click on Query Editor as shown below.
Step 4:
We just need to change In Memory Data Processing Threshold option, and change the threshold as per our requirement. As shown below, we have changed it from 5000 to 10000. 5000 is default value.
Conclusion:
Increasing the Memory Data Processing Threshold will resolve this error, but it will slow down your Azure Data Studio. Please remember to handle it carefully.
From the beginning of SQL Server, we used to filter data from any table with the help of WHERE Clause. Can we filter data withoutWHERE clause? The answer was a Big NO before Azure Data Studio but it is possible in Azure Data Studio. Curious?
Let me demostrate it with the help of examples.
Example 1: (Filter single column data)
Using SQL Server Management Studio (SSMS):
In SSMS, we use WHERE clause in order to filter the data as shown below.
USE AdventureWorks2019
GO
SELECT BusinessEntityID
, FirstName
, LastName
, ModifiedDate
FROM Person.Person
WHERE FirstName = 'Abraham';
GO
--OUTPUT
Using Azure Data Studio:
Step 1:
First of all, you need to write a query without WHERE clause and run it. After that just click on show filter button as highlighted below and it opens up the filter window.
Step 2:
In this step, you just need to select the respective value, which you want to filter. Since I selected FirstName as “Abraham” and pressed ok, it will show all employees having “Abraham” as first name as shown below.
Example 2: (Filter using wildcard techniques)
Using SQL Server Management Studio(SSMS):
In SSMS, we use WHERE clause in order to filter the data as shown below.
USE AdventureWorks2019
GO
SELECT EmailAddressID
, EmailAddress
, ModifiedDate
FROM Person.EmailAddress
WHERE EmailAddress LIKE 'Kendra0%' OR EmailAddress LIKE 'kendall0%';
GO
--OUTPUT
Using Azure Data Studio:
Step 1:
First of all, you need to write a query without WHERE clause and run it. After that, you just need to click on show filter button as highlighted below and it opens up the filter window.
Step 2:
In this step, you just need to select the respective value which you want to filter. Since I selected EmailAddress “kendra0% ” or “kendall0%” and pressed ok, it will show all EmailAddresses having “kendra0% ” or “kendall0%” as EmailAddress as shown below.
Conclusion:
I use Azure Data Studio for filtering the data and found it quite helpful and hassle free. Do let me know if you use it.
This blog/website is a personal blog/website and all articles, postings and opinions contained herein are my own. The contents of this blog/website are not intended to defame, purge or humiliate anyone should they decide to act upon or reuse any information provided by me. Comments left by any independent reader are the sole responsibility of that person. Should you identify any content that is harmful, malicious, sensitive or unnecessary, please contact me via email (imran@raresql.com) so I may rectify the problem.