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:
SELECT LEFT(LastName, 4) AS FirstFourLettersOfLastName, LastName FROM
dbo.DimEmployee
Results:
FirstFourLettersOfLastName
Gilb
Brow
Tamb
RIGHT Function: The RIGHT function retrieves the portion of the
string counting from the right.
For
example:
SELECT RIGHT(LastName, 4) AS FirstFourLettersOfLastName, LastName as
FullLastName FROM dbo.DimEmployee
Results:
LastFourLettersOfLastName
FullLastName
bert Gilbert
rown Brown
ello
Tamburello
SUBSTRING Function: SUBSTRING function retrieves a portion of the
string starting at the specified character and bringing back the number of
characters specified.
Syntax is:
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:
SELECT SUBSTRING(LastName, 3, 4) AS PortionOfLastName FROM
DimEmployee
Results:
PortionOfLastName
FullLastName
lber Gilbert
mbur
Tamburello
lter Walters
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:
SELECT
REVERSE(LastName) AS MirrorImage, LastName AS FullLastName
FROM DimEmployee
Results:
MirrorImage
FullLastName
trebliG
Gilbert
nworB Brown
ollerubmaT
Tamburello
sretlaW
Walters
REPLACE Function: REPLACE function replaces some characters within a string
with another set of characters.
Syntax is:
REPLACE(string expression, value to be replaced,
replacing value)
For example, the
following query replaces each occurrence of the word "payable" with
"receivable":
SELECT
AccountDescription, REPLACE(AccountDescription, 'payable', 'receivable') AS DreamOn FROM
dimAccount WHERE AccountDescription LIKE '%payable%'
STUFF Function: The STUFF function inserts a set of characters
into a given string at a given position.
Syntax
is:
STUFF(string to
manipulate, starting position, length, characters to insert)
For example, the following query adds "
town " string to every city in DimGeography table:
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':
SELECT STUFF(city, CHARINDEX('ville', city), 6, ' town ') AS Manipulated, City 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:
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.
Syntax is: REPLICATE(string, number of times).
For example, the following
query prints the string '100' five times:
SELECT REPLICATE('100', 5)
Result:
100100100100100
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:
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:
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.
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.
Syntax
of the function is:
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:
SELECT CHARINDEX("'",
LastName) AS
ApostrophePosition, LastName AS
FullLastName 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:
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:
SELECT PATINDEX('%ville%', city) AS Position, City FROM
dimGeography WHERE
city LIKE '%ville%'
Use
of PATINDEX function to display field value of table:
SELECT Address, PATINDEX('% %',Address) AS 'Index' FROM Customers
Output:
ContactName Index
Obere Str. 57 6
Avda. de la Constitución 2222 6
Mataderos
2312 10
120 Hanover Sq. 4
Berguvsvägen
8 13
Forsterstr. 57 12
Use
of PATINDEX function in where clause:
SELECT
ContactName, Address
FROM Customers
WHERE PATINDEX('%8%',Address) > 0
Output:
ContactName Address
Christina Berglund Berguvsvägen 8
Diego Roel C/ Moralzarzal, 86
Martine Rancé 184, chaussée de Tournai
Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35
Patricia McKenna 8
Johnstown Road
Jaime Yorres 87 Polk St. Suite 5
Above example displays all customers containing
character '8' in addresss from customers table.
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.
Syntax
: CHAR (
integer_expression )
Examples
of CHAR Function :
Use
of CHAR function in select clause
SELECT CHAR(83)
Output:
S
Above example displays the use of CHAR function
to display character based on ascii value.
STR Function: STR function is
used to convert numeric value to character.
Syntax
of STR Function :
STR ( float_expression [ ,length [ ,decimal ] ]
)
float_expression is an expression of float data
type with a decimal point.
length is total length including decimal point,
sign, digits, and spaces. The default is 10. If you specify negative value then
it returns NULL. It should be greater than or equal to the part of the number
before the decimal point plus it's sign. If it is not so then it returns ** .
decimal is the number of places after decimal
point. If you specify negative value then it returns NULL.
Return type of STR function is char.
Examples
of STR function :
Example
1 : Use of STR function in select clause
SELECT STR(137.45,7,3)
Output:
137.450
Above example can displays 6 digits and a
decimal point as seventh character. Fractional part of the number is rounded to
three decimal places.
Example
2 : Converting negative number to character type using STR function
SELECT STR(-137.45,6,1)
Output:
-137.4
Above example displays 4 digits, 5th character
as negative sign and 6th character as decimal point. Fractional part of the
number is rounded to one decimal place.
LTRIM : LTRIM function
returns the string by removing all the blank spaces at left side. It also takes
1 argument as string value.
Example:
select LTRIM('ASP ')
Result: ASP-----
Blanks at the right side not removed.
RTRIM : RTRIM function
returns the string by removing all the blank spaces at right side. It also
takes 1 argument as string value.
Example:
select RTRIM(' ASP')
Result: -----ASP
Blanks at the left side not removed.
No comments:
Post a Comment