String Functions - SQL Server 2008 -- CHARINDEX

CHARINDEX ( expression1, expression 2, [starting position]) :

This is one of the very important string functions in SQL Server. This function return the first position of a character or character string found within another character string. Here expression 1 is the string of character that would be searched in expression 2. Starting position is the index in expression 2 where CHARINDEX would start looking for expression 1 in expression 2.
The index of the first Character of expression2 is 1 .

Example:

expression 1 : you

expression 2 : Can you name the fastest animal on Earth. (MAX Length allowed in SS is 8000 characters)

Starting position : 0

In this case, we are trying to get the starting index of the expression1 if it exists in expression 2. SQL Server would start searching for the word "you" in expression 2 from left to right. If we did not specify the starting position, it is null , -ve or zero then the whole expression is searched starting from 1st position from left side.

In this particular case this function would return 5. Since in expression 2 the first character is at position 1, from there if you start counting, you would find the position of y (y of you) is 5th.





If expression 1 does not exist in expression 2 then in this case 0 is returned.

When to use it :
Most of the time when you need a specific substring in a string, you can use this function to get the starting/ending index of the substring.

Read More

How to find stored procedure with specific text in the code

I came across a situation where I had to find the list of all such stored procedures defined in a specific database containing at least one cursor in their code. The following query worked for me.

SELECT DISTINCT P.NAME FROM SYSCOMMENTS SC
INNER JOIN SYS.procedures P ON P.OBJECT_ID = SC.ID
AND P.NAME LIKE 'SP%' AND TEXT LIKE '%DECLARE%CURSOR%'

Read More

String Functions - SQL Server 2008

String functions operate on a string input value, and return a string or a numeric value. All built in string functions are deterministic, as they return the same result for a given set of values. Below we would discuss each function in detail with examples.

ASCII(character) :

This function returns ASCII values against the input character.

Example:

This function take single character as a parameter, if you provide it a string of character. It will give you the ASCII value for the first character in the string and will ignore rest of the characters in the string.

CHAR(integer) :

This functions returns a character value against a specific ASCII value.

Example:

Integer value given as a parameter range from 0-255. For any other value, it returns NULL.

LEN(string) :

This function returns number of characters in the given character string. It does not include the trailing blank spaces.

Example:

If you want to include the trailing spaces in string length, you need to use DATALENGTH() function.

RTRIM(expression) :

If you have a string and you want to get rid of the spaces from the right side of the string then you can use this function.

Here you can see that we have select a string with one space on its left and few spaces on its right.


now if we use Rtrim, these blank spaces from the right side of the resulting string would then be removed.




LTRIM(expression) :

LTRIM works exactly the same the RTRIM works however it works on the left side of the string. It removes any space from the left side of the string.

Read More

Functions in SQL Server 2008

A function performs operation on a given set of values and return a value. In Sql server, built in functions are either deterministic or non-deterministic.

Deterministic Functions:

A function is said to be deterministic if it returns same result at any time it is called for a specific set of input values.

DATEADD is a built-in deterministic function, as it returns the same value, when ever it is run on a specific set of values.

Non Deterministic Functions:

If a function returns different values each time it is called then it is said to non deterministic function.

GETDATE is a non deterministic function, as each time it is called it returns a different value. Date part returned by this function might be same but time part keeps changing for each call.

Read More