ALAGESAN PERIYASAMY's BLOG: sql server

Showing posts with label sql server. Show all posts

Thursday, 16 August 2012

SSAS MDX Queries part 2

Children Property

select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Country].[France].children on rows
from
[Adventure Works]

Exists Function

select
exists([Customer].[Customer Geography].[Customer].[Crystal Zheng],[Customer].[Customer Geography].members) on columns,
[Measures].[Sales Amount] ON ROWS
from
[Adventure Works]

Parent Function

select
[Measures].[Internet Sales Amount]on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent.parent on rows
from
[Adventure Works]

Ancestor Function

select
[Measures].[Internet Sales Amount] on columns,
ancestor([Customer].[Customer Geography].[Customer].
[Crystal Zheng],[Customer].[Customer Geography].[City]) on rows
from
[Adventure Works]

select
[Measures].[Internet Sales Amount]on columns,
ancestor([Customer].[Customer Geography].[Customer].[Crystal Zheng],2) on rows
from
[Adventure Works]

Ascendants Function

Returns all the ancestors of a specified member and the member itself up to the root of the member's hierarchy. Note that the specified member is returned first, then its parent, its grandparent and so on.

Example :

select
[Measures].[Internet Sales Amount]on columns,
ascendants([Customer].[Customer Geography].[Postal Code].&[OX14 4SE]&[Oxford]&[ENG]) on rows
from
[Adventure Works]

Ascendants with Hierarchize Function

Select
[Measures].[Internet Sales Amount]on columns,
hierarchize(ascendants([Customer].[Customer Geography].[Customer].[Crystal Zheng])) on rows
from
[Adventure Works]

FirstChild Function

select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent.firstchild on rows
from
[Adventure Works]

LastChild Function

select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent.lastchild
on rows
from
[Adventure Works]

Lastsibling Function

select [Measures].[Internet Sales Amount]on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].lastsibling on rows
from [Adventure Works]

Descendants Function

Syntax:

DESCENDANTS( «Member»[, «Level»[, «Desc_flags»]] )
DESCENDANTS( «Member»,«Distance»[, «Desc_flags»] )

According to MSDN, descendants MDX function returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
Desc_flags has 8 different flags option for returning set of descendant members. Here is description of each option mentioned in MSDN.

SELF Returns only descendant members from the specified level or at the specified distance. The function includes the specified member, if the specified level is the level of the specified member.

AFTER Returns descendant members from all levels subordinate to the specified level or distance.

BEFORE Returns descendant members from all levels between the specified member and the specified level, or at the specified distance. It includes the specified member, but does not include members from the specified level or distance.

BEFORE_AND_AFTER Returns descendant members from all levels subordinate to the level of the specified member. It includes the specified member, but does not include members from the specified level or at the specified distance.

SELF_AND_AFTER Returns descendant members from the specified level or at the specified distance and all levels subordinate to the specified level, or at the specified distance. S

SELF_AND_BEFORE Returns descendant members from the specified level or at the specified distance, and from all levels between the specified member and the specified level, or at the specified distance, including the specified member.

SELF_BEFORE_AFTER Returns descendant members from all levels subordinate to the level of the specified member, and includes the specified member.

Examples :

select
[Measures].[Internet Sales Amount] on columns,
descendants([Customer].[Customer Geography].[Country].&[United Kingdom],[Customer].[Customer Geography].[City])on rows
from [Adventure Works]

select
[Measures].[Internet Sales Amount] on columns,
descendants([Customer].[Customer Geography].[Country] .[France],[Customer].[Customer Geography].[City],self) on rows
from
[Adventure Works]

Descendants with after

select
[Measures].[Internet Sales Amount]on columns,
descendants([Customer].[Customer Geography].[Country].[France],[Customer].[Customer Geography].[City],after)on rows
from
[Adventure Works]

Descendants with before

select
[Measures].[Internet Sales Amount]on columns,
descendants([Customer].[Customer Geography].[Country].[France],[Customer].[Customer Geography].[City],before) on rows
from [Adventure Works]

Range Function

select
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2003]:
[Date].[Calendar].[Calendar Quarter].[Q3 CY 2003]
on columns,[Measures].[Reseller Sales Amount]on rows
from [Adventure Works]

Head(Top) Function

select
{[Measures].[Internet Sales Amount],[Measures].[Internet Tax Amount]}
on columns,head([Customer].[Customer Geography].[Country],2)
on rows from [Adventure Works]

--It will display first 2 countries from the dimension

Tail(Bottom) Function

select
{[Measures].[Internet Sales Amount],[Measures].[Internet Tax Amount]}
on columns,tail([Customer].[Customer Geography].[Country],3)
on rows from [Adventure Works]

--it will show last three members of the Country level.

SSAS MDX Queries part 1

Simple MDX query

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
[Product].[Category].MEMBERS ON ROWS
FROM [Adventure Works]

Hiding Nulls

select non empty {[Date].[Calendar].[Calendar Year],[Date].[Calendar]}
on columns,
{[Product].[Product Categories].[Category],[Product].[Product Categories]}
on rows

from [Adventure Works]

Introducing .members

select
{[Product].[Category].[Category].members,[Product].[Category].[All Products]} on columns,
[Measures].[Sales Amount] ON ROWS
from
[Adventure Works]

Individual Members

select
[Product].[Category].[Category].[Bikes] on columns,
[Measures].[Sales Amount] ON ROWS
from [Adventure Works]

Multiple Members

select
{[Product].[Category].[Bikes],[Product].[Category].[Clothing]} on columns ,
[Measures].[Sales Amount] ON ROWS
from [Adventure Works]

User Hierarchies

User hierarchies are created when dimensions are designed in BIDS. A user hierarchy is a
hierarchy that has three or more levels (including the All level at the top).

select
{[Product].[Product Categories].Allmembers}on columns
,{[Product].[Category].Allmembers} ON ROWS
from
[Adventure Works]WHERE [Measures].[Sales Amount]

Same Non-measure Dimension on Two Axes with Differing Hierarchies

select
[Date].[Calendar].[Calendar Year] on columns,
[Date].[Month of Year].[Month of Year] on rows
from [Adventure Works]

Cross Joins

If you crossjoin two sets of members from
the same dimension, the two sets must be based on different hierarchies within the
same dimension. You can even crossjoin two different dimension

Example 1 :
select
NONEMPTY({[Product].[Product Categories].Allmembers})on columns,
NONEMPTY(crossjoin([Date].[Calendar].[Calendar Year], [Date].[Month of Year].[Month of Year])) on rows
from
[Adventure Works] WHERE [Measures].[Internet Sales Amount]

Example 2:

select
{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]} on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from [Adventure Works]

Example 3:

select
crossjoin([Product].[Product Categories].[Category],{[Measures].[Internet Sales Amount],[Measures].[Reseller Sales Amount]}) on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from
[Adventure Works]

Example 4(Crossjoin on Two Separate Non-measure Dimensions)

select
crossjoin([Sales Territory].[Sales Territory].[Country],[Product].[Product Categories].[Category]) on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from
[Adventure Works]

Example 5:(Nested Cross Jons)

select
crossjoin([Sales Territory].[Sales Territory].[Country],crossjoin([Product].[Product Categories].[Category],
{[Measures].[Internet Order Count],[Measures].[Reseller Order Count]})) on columns,
crossjoin([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year]) on rows
from
[Adventure Works]

Alternate for Cross Joins

select
crossjoin([Sales Territory].[Sales Territory].[Country],[Product].[Product Categories].[Category],
{[Measures].[Internet Order Count],[Measures].[Reseller Order Count]})on columns,
([Date].[Calendar].[Calendar Year],[Date].[Month of Year].[Month of Year])on rows
from
[Adventure Works]

Friday, 19 August 2011

Built-in Functions - Cursor Functions

A cursor allows looping through a record set and performing a certain operation on each record within the set. SQL Server supports three functions that can help you while working with cursors: @@FETCH_STATUS, @@CURSOR_ROWS and CURSOR_STATUS. Cursor functions are non-deterministic.

A cursor life cycle can be described as follows:

Cursor is declared using the DECLARE CURSOR statement. This statement creates a cursor within SQL Server memory
Cursor is activated using OPEN CURSOR statement. At this point you can populate the cursor with a record set.
Data is retrieved from the cursor using the FETCH keyword.
A WHILE loop is executed within the cursor to perform some operation with the rows in the cursor with the condition that the FETCH command is successful.
Cursor is deactivated using CLOSE CURSOR statement. At this point you can't populate the cursor with additional rows. Nor can you work with rows within the cursor. However, you can re-open the cursor with OPEN CURSOR statement and perform additional work with the cursor.
The cursor is destroyed using DEALLOCATE CURSOR statement. Once the cursor is de-allocated it cannot be reopened.

@@FETCH

The most commonly used cursor function is @@FETCH_STATUS. This function determines whether FETCH keyword has successfully retrieved a row from the current cursor.

@@FETCH_STATUS can take one of the three values:

@@FETCH_STATUS value	Meaning
0	Successful fetch of a row within a cursor
-1	Fetch has failed. This could mean that the cursor has reached the beginning (or end) of the record set. This could also mean that we attempted retrieving a record that does not exist. For instance, if you attempt to grab 51st record within a cursor that has 50 records fetch status will be 1.
-2	The fetched row is missing. This means the record you're trying to FETCH has been deleted or its key has been updated since you have opened the cursor.

For example, the following cursor is populated with the top 5 customer names. While the cursor fetches rows successfully the @@FETCH_STATUS is 0. Once we get to the end of the result set @@FETCH_STATUS becomes -1:

DECLARE @customer_full_name VARCHAR(85)
DECLARE customer_cursor CURSOR FOR  SELECT TOP 5 FirstName + ' ' + MiddleName + ' ' + LastName FROM dimCustomer  
OPEN customer_cursor  
FETCH NEXT FROM customer_cursor INTO @customer_full_name
WHILE @@FETCH_STATUS = 0  
BEGIN   -- typically you'd do some row-based operation here  
FETCH NEXT FROM customer_cursor INTO @customer_full_name 
SELECT @@FETCH_STATUS AS fetch_status  
END 
CLOSE customer_cursor  
DEALLOCATE customer_cursor

Results:

fetch_status  
------------  
0
fetch_status  
------------  
0
fetch_status  
------------  
0
fetch_status  
------------  
0
fetch_status  
------------  
-1

@@CURSOR_ROWS

@@CURSOR_ROWS function returns the number of rows in the cursor which was opened last on the current connection. This means that if you have 3 cursors open @@CURSOR_ROWS will return the number of rows in the 3rd cursor. @@CURSOR_ROWS can take the following values:

@@CURSOR_ROWS value	Meaning
-m	Cursor is being populated asynchronously. "M" is the value of records in the record set.
-1	The cursor is DYNAMIC; that means, it reflects the changes to the data within the cursor. Therefore the number of rows can change due to addition or deletion of rows in the underlying tables. DYNAMIC cursors always return 1 as value of @@CURSOR_ROWS.
0	This can mean one of the following: Cursor has not been opened Cursor has no rows Cursor has been closed
N	Number of rows in the cursor record set. N is reported after the cursor has been fully populated

The following example shows you how @@CURSOR_ROWS value changes during the lifetime of the cursor:

DECLARE  @last_name  VARCHAR(20),
@first_name VARCHAR(20)
 
DECLARE MY_CURSOR CURSOR  FOR
SELECT TOP 3 LASTNAME,
FIRSTNAME
FROM   DIMCUSTOMER
 
SELECT 'cursor has ' + CAST(@@CURSOR_ROWS AS VARCHAR) + ' rows'
 
OPEN MY_CURSOR
 
FETCH NEXT FROM MY_CURSOR
INTO @last_name,
@first_name
 
SELECT 'cursor has ' + CAST(@@CURSOR_ROWS AS VARCHAR) + ' rows'
 
WHILE @@FETCH_STATUS = 0
BEGIN-- typically you'd do some row-based operation here  
FETCH NEXT FROM MY_CURSOR
INTO @last_name,
@first_name
END
 
CLOSE MY_CURSOR
 
SELECT 'cursor has ' + CAST(@@CURSOR_ROWS AS VARCHAR) + ' rows'
 
DEALLOCATE MY_CURSOR

Results:

----------------------------------------------  
cursor has 0 rows 
----------------------------------------------  
cursor has 3 rows 
----------------------------------------------  
cursor has 0 rows

If the same cursor is executed with DYNAMIC keyword against the entire DimCustomer table within Adventure Works database the results would show "cursor has -1 rows" while the cursor was being searched.

CURSOR_STATUS

The CURSOR_STATUS function can be used effectively within a stored procedure that calls another stored procedure, which returns an output parameter of CURSOR data type. This function can be used with local or global cursors and determines whether or not the stored procedure has returned a cursor with a result set. The syntax is:

1.CURSOR_STATUS( 'local' or 'global', cursor name)

1.CURSOR_STATUS ('variable', cursor variable name)

LOCAL or GLOBAL keywords allow you to specify the cursor scope; VARIABLE keyword specifies that CURSOR_STATUS function should examine a cursor variable.

In order to use the CURSOR data type as an output parameter, you must specify VARYING keyword along with OUTPUT within the CREATE PROCEDURE statement. The following example creates a procedure that returns a cursor as an output parameter:

CREATE PROCEDURE RETURN_CUSTOMER_NAMES(
@last_name VARCHAR(50),
@my_cursor CURSOR VARYING OUTPUT)
AS
BEGIN
SET NOCOUNT ON
 
SET @my_cursor =   CURSOR STATIC FOR   SELECT LastName, FirstName FROM DimCustomerWHERE LastName = @last_name
 
OPEN @my_cursor
END

Next, you can call this procedure from another procedure with a particular last name. The CURSOR_STATUS function will let you determine whether the cursor returned from the first procedure contains any rows, as follows:

CREATE PROCEDURE CALL_THE_OTHER_PROCEDURE
@last_name VARCHAR(50)
AS
SET NOCOUNT ON
 
DECLARE  @first_name VARCHAR(20)
 
DECLARE  @my_cursor CURSOR
 
/* now call the procedure returning a cursor output parameter */
EXECUTE RETURN_CUSTOMER_NAMES
@last_name ,
@my_cursor OUTPUT
 
IF CURSOR_STATUS('variable','@my_cursor') = 0
BEGIN
PRINT 'no records found'
 
RETURN
END
ELSE
BEGIN
FETCH NEXT FROM @my_cursor
INTO @last_name,
@first_name
 
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @last_name,
@first_name
 
FETCH NEXT FROM @my_cursor
INTO @last_name,
@first_name
END
 
CLOSE @my_cursor
 
DEALLOCATE @my_cursor
END

Once the two procedures are created you can execute the caller procedure with the last name "Huang" as follows:

1.EXECUTE call_the_other_procedure 'huang'

Results (abbreviated):

-------------------------------------------------- --------------------  
Huang  Eugene  
-------------------------------------------------- --------------------  
Huang  Erica  
-------------------------------------------------- --------------------  
Huang  Alejandro  
-------------------------------------------------- --------------------  
Huang  Shannon

The following table summarizes the values returned by CURSOR_STATUS function:

CURSOR_STATUS value	Meaning for variable	Meaning for cursor name
1	Cursor is open and has at least 1 row DYNAMIC cursors return 1 even if they contain no rows	Cursor is open and has at least 1 row DYNAMIC cursors return 1 even if they contain no rows
0	Cursor is open but has no rows	Cursor has no rows
-1	The cursor is closed	The cursor is closed
-2	Cursor wasn't returned by the called procedure OR the cursor was deallocated prior to being assigned to this variable	The value of 2 is never returned by CURSOR_STATUS if you refer to cursor name
-3	Cursor variable with the specified name does not exist OR the variable exists, but it hasn't been assigned to a cursor returned from the called procedure	Cursor with the specified name does not exist

The following example shows how the values returned by the CURSOR_STATUS function change during the cursor's lifecycle:

SET NOCOUNT ON 
 
SELECT 'cursor hasn''t been declared; the status is: ' + CAST(CURSOR_STATUS('global','my_cursor') AS VARCHAR)
 
DECLARE  @last_name  VARCHAR(20),
@first_name VARCHAR(20)
 
DECLARE MY_CURSOR CURSOR GLOBAL FOR
SELECT LASTNAME,
FIRSTNAME
FROM   DIMCUSTOMER
WHERE  LASTNAME = 'white'
 
SELECT 'cursor is declared; the status is: ' + CAST(CURSOR_STATUS('global','my_cursor') AS VARCHAR)
 
OPEN MY_CURSOR
 
SELECT 'cursor is opened; the status is: ' + CAST(CURSOR_STATUS('global','my_cursor') AS VARCHAR)
 
FETCH NEXT FROM MY_CURSOR
INTO @last_name,
@first_name
 
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @last_name,
@first_name
 
FETCH NEXT FROM MY_CURSOR
INTO @last_name,
@first_name
END
 
CLOSE MY_CURSOR
 
SELECT 'cursor is closed; the status is: ' + CAST(CURSOR_STATUS('global','my_cursor') AS VARCHAR)
 
DEALLOCATE MY_CURSOR
 
SELECT 'cursor is deallocated; the status is: ' + CAST(CURSOR_STATUS('global','my_cursor') AS VARCHAR)

Results (abbreviated):

--------------------------------------------------------------------------  
cursor hasn't been declared; the status is: -3  
-----------------------------------------------------------------  
cursor is declared; the status is: -1  
---------------------------------------------------------------  
cursor is opened; the status is: 1 
-------------------- --------------------  
WhiteIan  
---------------------------------------------------------------  
cursor is closed; the status is: -1  
--------------------------------------------------------------------  
cursor is deallocated; the status is: -3

Built-in Functions - String Functions

LEFT Function

Transact-SQL supports retrieving portions of the string. For instance, to retrieve the first few characters from the left of the string you use the LEFT function. The following example retrieves first four letters of employee last names in the AdventureWorksDW database:

1.SELECT LEFT(LastName, 4) AS FirstFourLettersOfLastName,  LastName FROM dbo.DimEmployee

Results (abbreviated):

FirstFourLettersOfLastName  
Gilb  
Brow  
Tamb  
Walt  
Walt

RIGHT Function

The RIGHT function retrieves the portion of the string counting from the right. For example:

1.SELECT RIGHT(LastName, 4) AS FirstFourLettersOfLastName,  LastName as FullLastName FROM dbo.DimEmployee

Results:

LastFourLettersOfLastName FullLastName  
bert Gilbert  
rown Brown  
ello Tamburello  
ters Walters  
ters Walters

LTRIM and RTRIM Functions

Notice that RIGHT and LEFT functions don't check for blank characters. In other words if your string contains a couple of leading blanks then LEFT(string_variable, 2) will return you two blank spaces, which might not be exactly what you want. If your data needs to be left aligned you can use LTRIM function, which removes the leading blanks. Similarly the RTRIM function removes the trailing characters. For instance, the following UPDATE statement will left align (remove any number of leading blanks) last names:

1.UPDATE DimEmployee SET LastName = LTRIM(LastName)

Similarly, if your data is padded with spaces and you don't wish to see spaces in your output you can use the RTRIM function. You could combine the two functions to remove both leading and trailing spaces as follows:

1.UPDATE DimEmployee SET LastName = LTRIM(RTRIM(LastName))

SUBSTRING Function

SUBSTRING function retrieves a portion of the string starting at the specified character and bringing back the number of characters specified; the syntax is:

1.SUBSTRING(string, starting_character_number, number_of_characters_to_return)

The following example will retrieve four characters from the employee last names, starting at the third character:

1.SELECT SUBSTRING(LastName, 3, 4) AS PortionOfLastName FROM DimEmployee

Results:

PortionOfLastName FullLastName  
lber Gilbert  
mbur Tamburello  
lter Walters  
lter Walters

Notice that the SUBSTRING function finds the starting character by counting from the left. In other words, if we run SUBSTRING(LastName, 3, 4) against the last name of "Buchanan" we start on the 3rd character from the left - "c".

REVERSE Function

The REVERSE function gives you a mirror image of a given string. The following example returns the mirror image of employee last names:

1.SELECT  REVERSE(LastName) AS MirrorImage,  LastName AS FullLastName  FROM DimEmployee

Results:

MirrorImage FullLastName  
trebliG Gilbert  
nworB Brown  
ollerubmaT Tamburello  
sretlaW Walters  
sretlaW Walters

CHARINDEX and PATINDEX Function

Transact-SQL supports two functions for finding an occurrence of a particular character (or number of characters) within a string: CHARINDEX and PATINDEX. CHARINDEX finds the starting position of a single character or string within a given column (or variable). In addition, if you suspect that the value you are searching for might occur multiple times within a given string you can specify the character number at which you want to start searching. The syntax of the function is:

1.CHARINDEX(search value, string, starting search location)

For example, you might wish to find a position of an apostrophe inside employee last names. The following query shows how this can be achieved using CHARINDEX function:

1.SELECT  CHARINDEX('''', LastName) AS ApostrophePosition,  LastName AS FullLastName   
2.FROM DimEmployee  WHERE lastname LIKE '%''%'

Results:

ApostrophePosition FullLastName  
2 D'Hers  
2 D'sa

Perhaps a more interesting example is finding an occurrence of a string, as opposed to an occurrence of a character. For example, city names often end with "ville", as in Huntsville or Clarksville. The following query finds the starting position of "ville" within city names:

1.SELECT  CHARINDEX('ville', city) AS Position,  City    FROM dimGeography  WHERE city LIKE '%ville'

Results:

Position City  
5 Daleville  
10 Campbellsville  
4 Melville  
6 Crossville  
5 Maryville

The next example finds the occurrence of the value within a variable, starting search at the 20th character:

DECLARE @variable VARCHAR(255) 
SELECT @variable = 'this is a string. this is also a string' 
SELECT  CHARINDEX('string', @variable, 20) AS Position

Results:

Position  
-----------  
34

PATINDEX function is very similar to CHARINDEX - it also finds the position of the first occurrence of a character or multiple characters. The difference is that you have to append % wildcards to PATINDEX; this function searches for a pattern. If you use a % wildcard with CHARINDEX you won't find anything, unless your data contains percent signs. If you're searching for a pattern at the end of the string expression, then you only have to use the % wildcard at the beginning of the pattern to be found, as in PATINDEX ('%pattern_to_find', string). The following query returns the same results as CHARINDEX example:

1.SELECT  PATINDEX('%ville%', city) AS Position,  City FROM dimGeography  WHERE city LIKE '%ville%'

REPLACE Function

REPLACE function replaces some characters within a string with another set of characters. The syntax is:

1.REPLACE(string expression, value to be replaced, replacing value)

For example, the following query replaces each occurrence of the word "payable" with "receivable":

1.SELECT AccountDescription,  REPLACE(AccountDescription, 'payable', 'receivable') AS DreamOn   
2.FROM dimAccount  WHERE AccountDescription LIKE '%payable%'

STUFF Function

The STUFF function inserts a set of characters into a given string at a given position. The syntax is:

1.STUFF(string to manipulate, starting position, length, characters to insert)

For example, the following query adds " town " string to every city in DimGeography table:

1.SELECT  STUFF(city, 5, 6, ' town ') AS Manipulated,  City  FROM dimGeography

Results:

Manipulated City  
Rock town n Rockhampton  
Town town  Townsville  
Clov town  Cloverdale  
Find town  Findon  
Pert town  Perth

You saw how to find the position of a specific character or number of characters using CHARINDEX. Now you can apply that knowledge and use STUFF function to replace characters based on their position.

The following example determines the position of 'ville' in the City column and then replaces it with 'town':

1.SELECT STUFF(city, CHARINDEX('ville', city), 6, ' town ') AS Manipulated,  City 
2.FROM dimGeography  WHERE city LIKE '%ville'

Results:

Manipulated City  
Campbells town  Campbellsville  
Mel town  Melville  
Cross town  Crossville  
Mary town  Maryville  
Nash town  Nashville

LEN Function

The LEN function finds the length of the character string. The function takes the string as a single argument. For example, the following query shows the length of each city name:

1.SELECT LEN(city) AS number_of_characters, City FROM dimGeography

Results:

Number_of_characters City  
10 Alexandria  
13 Coffs Harbour  
12 Darlinghurst  
8 Goulburn  
9 Lane Cove

Note: use the DATALENGTH system function to determine the number of characters in a TEXT column

REPLICATE Function

The REPLICATE function repeats a given string specified number of times. The syntax is: REPLICATE(string, number of times). For example, the following query prints the string '100' five times:

1.SELECT REPLICATE('100', 5)

Result:

1.100100100100100

One common usage of REPLICATE function is to combine it with other string functions and replace leading or trailing spaces with another character.

DECLARE @StringWithLeadingSpaces VARCHAR(10)   
SELECT @StringWithLeadingSpaces= '     SD3L6AA'   
SELECT @StringWithLeadingSpaces =  REPLICATE('0', LEN(@StringWithLeadingSpaces)
- LEN(LTRIM(@StringWithLeadingSpaces)))  + LTRIM(@StringWithLeadingSpaces) 
SELECT @StringWithLeadingSpaces AS StringWithOUTLeadingSpaces

Result:

StringWithOUTLeadingSpaces  
--------------------------  
00000SD3L6

SPACE Function

The SPACE function is an equivalent of using REPLICATE to repeat spaces. This function takes a single argument - number of spaces you want to print.

UPPER and LOWER Functions

UPPER and LOWER functions change the case of the query's output. Both functions accept a string expression as the only argument. For example, the following query will return the US cities and corresponding states in mixed case:

SELECT UPPER(LEFT(City, 1)) +  LOWER(SUBSTRING(City, 2, (LEN(City) - 1))) + ',' + SPACE(2)
+ UPPER(LEFT(StateProvinceName, 1)) +  LOWER(SUBSTRING(StateProvinceName, 2,
(LEN(StateProvinceName) - 1)))  AS CityAndState
FROM DimGeography  WHERE CountryRegionCode = 'us'

Results (abbreviated):

CityAndState  
Chandler,  Arizona  
Gilbert,  Arizona  
Mesa,  Arizona  
Phoenix,  Arizona  
Scottsdale,  Arizona

ASCII Function

ASCII function returns the ASCII code value of the leftmost character of a string. This function is commonly used for comparing characters without knowing whether they're in upper or lower case. Upper case and lower case letters translate into different ASCII values, as the following example shows:

1.SELECT ASCII('A') AS UpperCase, ASCII('a') AS LowerCase

Results:

UpperCase   LowerCase  
----------- -----------  
65          97

UNICODE Function

UNICODE function works just like ASCII, except it accepts the Unicode character value as input. This could be useful if you're working with international character sets.

CHAR Function

The CHAR function does the opposite of ASCII - it returns an alphanumeric equivalent of an ASCII code. CHAR function accepts a single argument - a number between 0 and 255. It is often necessary to append a carriage return, line feed, or both to the query output. In such cases you can effectively use CHAR function, as follows:

1.SELECT   'My Output'   + --Add three carriage returns and a line feed:
2.REPLICATE(CHAR(10), 3) + CHAR(13)  + 'AnotherOutput'

Results:

------------------------  
My Output       
AnotherOutput

NCHAR Function

NCHAR function works exactly like CHAR except it returns the Unicode character. This function is useful if you're working with large international character sets. Unlike CHAR function NCHAR can handle values between 0 and 65535.

QUOTENAME Function

The QUOTENAME function appends square brackets to the beginning and end of the string expression and thereby makes a string expression a valid SQL Server identifier. QUOTENAME function is useful when working with database object names that contain spaces and reserved words. Generally it's a bad idea to use reserved words, special characters and spaces inside your object names. However at times, such as when working with 3rd party software, you do not have a choice. The following example uses QUOTENAME function to create a valid identifier:

1.SELECT QUOTENAME('column name with spaces')

Results:

1.[column name with spaces]

STR Function

The STR function converts a numeric value into a string. This function can be considered as a special case of CAST or CONVERT functions, both of which let you convert the variable from one data type into another compatible datatype. The STR function allows specifying the length of the string variable returned, as well as how many decimal points to include in the output. The syntax is:

1.STR(numeric value, length, decimal)

The length argument specifies the total length of the string. For example, '2.4503' consists of 6 characters. The decimal argument specifies how many characters are allowed to the right of the decimal point. For example, the following query returns a rounded currency rate as a string; the string has a total of six characters, five of which could be to the right of the decimal point:

1.SELECT  EndOfDayRate,  STR(EndOfDayRate, 6, 4) AS string_value
2.FROM factCurrencyRate  WHERE CurrencyKey = 3 AND timeKey = 2

Results:

1.EndOfDayRate string_value  
2.1.000900811 1.0009

SOUNDEX and DIFFERENCE Functions

The other two string functions available in SQL Server are SOUNDEX and DIFFERENCE, which happen to be rather similar and very seldom used. SOUNDEX provides a four character representation of the string (SOUNDEX code) and is supposed to help you determine whether two strings sound alike. For example, the following query retrieves SOUNDEX values for a few employees:

1.SELECT SOUNDEX(LastName) AS soundex_code, LastName FROM DimEmployee

Results:

soundex_code string_value  
S650 Sharma  
S100 Shoop  
S150 Spoon  
S520 Song  
S520 Singh  
S550 Simon  
S530 Smith

The DIFFERENCE function provides a degree of similarity (or lack thereof) between the two character expressions. If the SOUNDEX values are the same for the two strings passed to the DIFFERENCE function then the degree of similarity is the highest - 4. Otherwise, the DIFFERENCE function will return 3, 2, 1 or 0. The DIFFERENCE function could be used when you wish to find all customers with a name that sounds similar to a known value, as in the following example:

1.SELECT LastName FROM DimEmployee WHERE DIFFERENCE (LastName, 'que') > 2

Results:

LastName  
Mu  
Liu  
Wu  
Liu  
Poe  
Li  
Loh  
Nay

ALAGESAN PERIYASAMY's BLOG

Thursday, 16 August 2012

SSAS MDX Queries part 2

SSAS MDX Queries part 1

Friday, 19 August 2011

Built-in Functions - Cursor Functions

@@FETCH

@@CURSOR_ROWS

CURSOR_STATUS

Built-in Functions - String Functions

LEFT Function

RIGHT Function

LTRIM and RTRIM Functions

SUBSTRING Function

REVERSE Function

CHARINDEX and PATINDEX Function

REPLACE Function

STUFF Function

LEN Function

REPLICATE Function

SPACE Function

UPPER and LOWER Functions

ASCII Function

UNICODE Function

CHAR Function

NCHAR Function

QUOTENAME Function

STR Function

SOUNDEX and DIFFERENCE Functions

Blog Archive

About Me