SQL FUNCTION

SQL Function

To Check Version of SQL Server through Query

Select @@Version

[http://www.1keydata.com/sql/sql-substring.html]

1] Concatenate

Sometimes it is necessary to combine together (concatenate) the results from several different fields. Each database provides a way to do this:

MySQL: CONCAT()

Oracle: CONCAT(), ||

SQL Server: +

The syntax for CONCAT() is as follows:

CONCAT (str1, str2, str3 ...): Concatenate str1, str2, str3, and any other strings together. Please note the Oracle CONCAT() function only allows two arguments -- only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using '||'.

MySQL/Oracle:
SELECT CONCAT(region_name,store_name) FROM Geography
WHERE store_name = 'Boston';

Result:

'EastBoston'

Example 2:

Oracle:
SELECT region_name || ' ' || store_name FROM Geography
WHERE store_name = 'Boston';

Result:

'East Boston'

Example 3:

SQL Server:
SELECT region_name + ' ' + store_name FROM Geography
WHERE store_name = 'Boston';

Result:

'East Boston'

2] Substring

The Substring function in SQL is used to grab a portion of the stored data. This function is called differently for the different databases:

MySQL: SUBSTR(), SUBSTRING()

Oracle: SUBSTR()

SQL Server: SUBSTRING()

The most frequent uses are as follows (we will use SUBSTR() here):

SUBSTR(str,pos): Select all characters from starting with position . Note that this syntax is not supported in SQL Server.

SUBSTR(str,pos,len): Starting with the th character in string and select the next characters.

Example 1:

SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';

Result:

's Angeles'

Example 2:

SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';

Result:

'an D'

3] TRIM

The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces. This function is called differently in different databases:

• MySQL: TRIM(), RTRIM(), LTRIM()
• Oracle: RTRIM(), LTRIM()
• SQL Server: RTRIM(), LTRIM()

The syntax for these trim functions are:

TRIM ([[LOCATION] [remstr] FROM ] str): [LOCATION] can be either LEADING, TRAILING, or BOTH. This function gets rid of the [remstr] pattern from either the beginning of the string or the end of the string, or both. If no [remstr] is specified, white spaces are removed.

LTRIM(str): Removes all white spaces from the beginning of the string.

RTRIM(str): Removes all white spaces at the end of the string.

Example 1:

SELECT TRIM(' Sample ');

Result:

'Sample'

Example 2:

SELECT LTRIM(' Sample ');

Result:

'Sample '

Example 3:

SELECT RTRIM(' Sample ');

Result:

' Sample'

To get email Id in one column

Select EmailId from tbl_CharitableEmails union Select User_EmailId from tbl_User_Login