Function inn Sql Server

select Ascii('a')

1 Program
SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position
-- and for the character string.
DECLARE @position int, @string char(15)
-- Initialize the variables.
SET @position = 1
SET @string = 'Du monde entier'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
SET NOCOUNT OFF
GO


LEFT
Returns the left part of a character string with the specified number of characters.

Syntax
LEFT ( character_expression , integer_expression )



SELECT LEFT(title, 5)
FROM titles
ORDER BY title_id


LEN
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

Syntax
LEN ( string_expression )

Arguments
string_expression

Is the string expression to be evaluated.

Return Types
int

Examples
This example selects the number of characters and the data in CompanyName for companies located in Finland.

USE Northwind
GO
SELECT LEN(CompanyName) AS 'Length', CompanyName
FROM Customers
WHERE Country = 'Finland'


LOWER
Returns a character expression after converting uppercase character data to lowercase.

Syntax
LOWER ( character_expression )

Arguments
character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

Return Types
varchar

Examples
This example uses the LOWER function, the UPPER function, and nests the UPPER function inside the LOWER function in selecting book titles that have prices between $11 and $20.

USE pubs
GO
SELECT LOWER(SUBSTRING(title, 1, 20)) AS Lower,
UPPER(SUBSTRING(title, 1, 20)) AS Upper,
LOWER(UPPER(SUBSTRING(title, 1, 20))) As LowerUpper
FROM titles
WHERE price between 11.00 and 20.00

LTRIM
Returns a character expression after removing leading blanks.

Syntax
LTRIM ( character_expression )

Arguments


Examples
This example uses LTRIM to remove leading spaces from a character variable.

DECLARE @string_to_trim varchar(60)
SET @string_to_trim = ' Five spaces are at the beginning of this
string.'
SELECT 'Here is the string without the leading spaces: ' +
LTRIM(@string_to_trim)



REPLACE
Replaces all occurrences of the second given string expression in the first string expression with a third expression.

Syntax
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )



REPLICATE
Repeats a character expression for a specified number of times.



SELECT Replicate(username+' ',2) as UserName from FMP_tbluser

Result
UserName
------------
testtest
user1user1
user2user2

SELECT REPLACE('abcdefghicde','cde','xxx')
GO
Here is the result set:

------------
abxxxfghixxx
(1 row(s) affected)



REVERSE
Returns the reverse of a character expression.

SELECT Reverse(username) as UserName from FMP_tbluser
UserName
-------
tset
1resu
2resu


Select username from FMP_tbluser

UserName
------
test
user1
user2


SELECT Right(username,2) as UserName from FMP_tbluser
UserName
-------
st
r1
r2



SPACE
Returns a string of repeated spaces.


Select username+space(20)+username as username from FMP_tbluser
UserName
--------
test test
user1 user1
user2 user2

Select username+space(10)+username as username from FMP_tbluser
UserName
--------


test test
user1 user1
user2 user2




SUBSTRING
Returns part of a character, binary, text, or image expression. For more information about the valid Microsoft® SQL Server™ data types that can be used with this function, see Data Types.

Syntax
SUBSTRING ( expression , start , length )

SELECT substring(username,1,2) as UserName from FMP_tbluser

UserName
--------
te
us
us