LIKE in SQL

Facebooktwitterredditpinterestlinkedinmail

The LIKE operator in SQL Server allows you to query data using patterns instead of exact matches. Normal select statements using the = operator will only return records where there is an exact match (usually the casing or trailing spaces do not matter). With the LIKE operator, it does not need to be an exact match.

LIKE
The most basic concept of the like operator is that it allows you to query data without using an exact match in the WHERE clause. It allows you to use some wildcard characters to get the results you are looking for. Here is a basic example:

SELECT	*
FROM	Animal
WHERE	AnimalName LIKE 'Giant %'

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,Animal Weight
35,Giant Squid,Fish,400
36,Giant Panda,Mammal,500
37,Giant Clam,Fish,60
[/table]

The above statement uses the % as a wildcard. It basically says, show me any animals that start with the word Giant. This would return things like Giant Panda, Giant Squid, and Giant Clam.

Although % is the most common wildcard that is used with LIKE, there are many more. Here is a list of all the allowed wildcards.

%
Will allow zero or any characters. You can use this before, after, or in-between any string.

SELECT	*
FROM	Animal
WHERE	AnimalName LIKE 'GIANT %'
	OR AnimalName LIKE '% Penguin'
	OR AnimalName LIKE 'Hammer% Shark'

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,Animal Weight
35,Giant Squid,Fish,400
36,Giant Panda,Mammal,500
37,Giant Clam,Fish,60
38,Emperor Penguin,Bird,15
39,Hammer Head Shark,Fish,90
[/table]

 
_
Will allow any 1 character.

SELECT	*
FROM	Animal
WHERE	AnimalName LIKE 'Chicke_'

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,Animal Weight
18,Chicken,Bird,5
[/table]

 
[]
Will allow 1 character that is specified in the brackets. There are two ways to specify this. [a-d] or [abcd].

The following example will match both goose and moose.

SELECT	*
FROM	Animal
WHERE	AnimalName LIKE '[gm]oose'

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,Animal Weight
11,Goose,Bird,15
27,Moose,Mammal,1000
[/table]

 
The following example will match both cat and bat, but it will NOT match rat.

SELECT	*
FROM	Animal
WHERE	AnimalName LIKE '[a-d]at'

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,Animal Weight
10,Cat,Mammal,10
34,Bat,Bird,1
[/table]

 
[^]
Will match any 1 character that is NOT specified in the brackets. There are two ways to specify this. [^a-d] or [^abcd].

The following example will match horse, but will NOT match zorse… and yes, zorse is an animal… I found it on the internet.

SELECT	*
FROM	Animal
WHERE	AnimalName LIKE '[^z]orse'

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,Animal Weight
6,Horse,Mammal,750
[/table]

 
The following example will match rat, but will NOT match bat or cat.

SELECT	*
FROM	Animal
WHERE	AnimalName LIKE '[^a-d]at'

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,Animal Weight
33,Rat,Mammal,1
[/table]

 
Important
If you are comparing a CHAR data type with the like operator, it may not work correctly. This because when you save data to a CHAR field it will space pad the field to the size of the field. To get around this, you need to trim the spaces from the end of the field.

SELECT	*
FROM	Animal
WHERE	RTRIM(AnimalName) LIKE '%monkey'

 
NOT Like
If you want to match where a pattern is NOT like a string, simply put the word NOT in front of the word LIKE.

SELECT	*
FROM	Animal
WHERE	AnimalName NOT LIKE ‘%monkey’

 
 
Escaping The Wildcard
From time to time you will need to actually search for a pattern containing one of the wildcard characters. Let’s say that you wanted to search for anything in a string that had 10% in it. The % (percent symbol) is a reserved word with the LIKE operator. You would search for the % character by using the ESCAPE clause.

SELECT	*
FROM	Invoice
WHERE	LineItemDescription LIKE '10!%%' ESCAPE '!'

In the query above, you can see that !% in the comparison string means to the literal character %. The ESCAPE clause tells the query to not apply the wildcard rules to any character following the specified characters. In this case, we are specifying the ! (exclamation mark).

[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
InvoiceID,ItemName,LineItemDescription
1,Coupon Code,10% off web coupon
[/table]

 
Reference: https://msdn.microsoft.com/en-us/library/ms179859.aspx

How To Pull Single Value From XML

Facebooktwitterredditpinterestlinkedinmail

SQL Server has a built in method for easily pulling one value from an XML. To do this you use the method called .value(). The .value method will run an XQuery against the XML specified in the query. This method is scalar, so it will only return 1 value. You cannot use this to return multiple values.

DECLARE	@XMLToParse  XML
 
-- Load the XML data in to a variable to work with.
-- This would typically be passed as a parameter to a stored procedure
SET	@XMLToParse =	'
						Baboon
					 '

SELECT  @XMLToParse.value('data(/Animal/AnimalName)[1]',
							'VARCHAR(MAX)') AS AnimalName

In the query above, you can see that you can just pass the xpath and the datatype that you want the output to be. This will pull that value out of the XML and put it in the specified format. You can see that after the xpath, there is a funny syntax [1]. This tells SQL Server to grab the first instance of the AnimalName node. You need to do this because the .value() method only returns one value and will not work with a repeating node.

Reference: https://msdn.microsoft.com/en-us/library/ms178030.aspx

SQL Server Replace

Facebooktwitterredditpinterestlinkedinmail

Replacing text in SQL Server is easy. SQL Server has the REPLACE function to perform this task.

SQL Server REPLACE
The REPLACE function in SQL Server has 3 parameters.

  1. Text to search
  2. Text to find
  3. Text to replace with
SELECT	REPLACE('Full text to search in', 'search', 'replace') AS ReplacedText

The output is: Full text to replace in

In the above example, we are searching “Text to search in” for the word “search” and we are replacing it with the word “replace”. Pretty simple, right? Now let’s take it a step further.

SQL Server Case Sensitive Replace
Above we went over how to do a case insensitive replace on a string. (The REPLACE function actually uses the default collation of the input text that it is searching). To turn it in to a SQL Server case sensitive replace, we just need to add one small thing to the end. We need to change the collation of the text we are searching. Learn more about text collation here.

SELECT REPLACE('Full text to search in' COLLATE SQL_Latin1_General_CP1_CS_AS, 'SEARCH', 'replace') AS ReplacedText

The output is: Full text to search in

This example shows how the “SEARCH” text was not found because we are changing the input text to search collation to SQL_Latin1_General_CP1_CS_AS (which is case sensitive). The input string has “search” in lower case and we were searching for an upper case “SEARCH”.

 
Reference: http://msdn.microsoft.com/en-us/library/ms186862(v=sql.120).aspx

SQL Rank Vs Row Number

Facebooktwitterredditpinterestlinkedinmail

SQL Rank Vs Row Number is a common question because the two functions are very similar. SQL Server has the two similar built-in functions to assign numbers to the order of the data in the result set. The SQL Server RANK function and the ROW_NUMBER function both can accomplish this, but they do something slightly different. You can follow these links see details on how to use this functions at RANK function and ROW_NUMBER function.

ROW_NUMBER: Returns a sequential number starting at 1 in the order specified. If there are duplicate records, SQL Server will continue the sequence on the duplicated record… ensuring that the row number sequence is never duplicated.

RANK: Returns a sequential number starting at 1 in the order specified. If there are duplicate records, SQL Server will use the same number for the duplicate records. There will then be a gap in the sequence for the duplicated records.

 
Reference:
    RANK: http://msdn.microsoft.com/en-us/library/ms176102.aspx
    ROW_NUMBER: http://msdn.microsoft.com/en-us/library/ms186734.aspx

Ranking Data In SQL Server

Facebooktwitterredditpinterestlinkedinmail

When it comes to ranking data in SQL Server, as of SQL Server 2005 they have built this in to the database. In 2005 the SQL Server team introduced the RANK function. The RANK function allows you to assign a number to each record in a result set based something in the data.

Let’s say that you wanted to apply a rank based on the weight of animals in your zoo (from heaviest to lightest). Elephant would get a number of 1 (because it’s the heaviest) and a tiny turtle would get the number 100 (because it’s the lightest). The number returned would start at 1 and grow for every record. This is done using this awesome RANK function.

The RANK function also has a partition feature that allows you to group the data. This allows the ranking number to reset for every group. Using our example above, we could extend it to show us the heaviest to lightest animals based on the animal type. So there would be a #1 ranking for the mammal, amphibian, bird, and reptile animal types.

 
Duplicate Ranks
Because the ranking number is incremented in the order specified by the ORDER BY clause, what happens if there are duplicates? In our example above, what if there are 2 animals with the same weight? If this occurs, the same ranking number will be applied to each of the duplicate records. So if there were 2 animals with the same weight, they would get the same exact ranking number.

 
How To Use RANK
The RANK function has 2 parameters. You must always supply the ORDER BY. The PARTITION BY is optional.

    PARTITION BY – This is what you would like SQL Server to group your rankings by. In the above example, if we want the ranking number to reset for every different animal types, then we would specify that here.
    ORDER BY – The ORDER BY is the order that you want your ranking number to be generated in your result set. This is a required parameter. In the example above, we want it from heaviest to lightest. We would specify the column name here that holds the animal weight.

Here is some sample code and the output to show each of the above scenarios.

SELECT	AnimalID,
	AnimalName,
	AnimalWeight,
	RANK() OVER (ORDER BY AnimalWeight DESC) AS AnimalWeightRank
FROM	Animal

In the above example, you can see that we are ranking each record by the weight of the animal from heaviest to lightest. This is done by passing the ORDER BY parameter to the function.

[table width=”500″ colwidth=”20|75|50|20″ colalign=”left|left|left|left”]
AnimalID,AnimalName,AnimalWeight,AnimalWeightRank
168,Camel,1200,1
162,Carabao,500,2
163,Cattle,400,3
166,Alpaca,350,4
182,Deer,300,5
187,Turkey,30,6
186,Goose,15,7
185,Duck,10,8
171,Cat,10,8
188,Quail,10,8
189,Pigeon,5,11
184,Chicken,5,11
[/table]

 
 

SELECT	AnimalID,
	AnimalName,
	AnimalType,
	AnimalWeight,
	RANK() OVER (PARTITION BY AnimalType ORDER BY AnimalWeight DESC) AS AnimalWeightRank
FROM	Animal

In the above example, you can see that we are ranking each record by the weight of the animal from heaviest to lightest. We take this a bit further by grouping these by the AnimalType. This is done by passing the PARTITION BY parameter to the function call.

[table width=”500″ colwidth=”20|75|75|50|20″ colalign=”left|left|left|left|left”]
AnimalID,AnimalName,AnimalType,AnimalWeight,AnimalWeightRank
187,Turkey,Bird[attr style=”background-color:#F2F5A9″],30,1[attr style=”background-color:#F2F5A9″]
186,Goose,Bird,15,2
185,Duck,Bird,10,3
188,Quail,Bird,10,3
189,Pigeon,Bird,5,5
184,Chicken,Bird,5,5
168,Camel,Mammal[attr style=”background-color:#F2F5A9″],1200,1[attr style=”background-color:#F2F5A9″]
162,Carabao,Mammal,500,2
163,Cattle,Mammal,400,3
166,Alpaca,Mammal,350,4
182,Deer,Mammal,300,5
171,Cat,Mammal,10,6
[/table]

 
 
Reference: http://msdn.microsoft.com/en-us/library/ms176102.aspx