|
Sql string function is a built-in string
function.
It perform an operation on a string input value and return a string or numeric
value.
Below is All built-in Sql string function :
ASCII, NCHAR,
SOUNDEX, CHAR,
PATINDEX,
SPACE, CHARINDEX,
REPLACE, STR,
DIFFERENCE, QUOTENAME,
STUFF,
LEFT,
REPLICATE,
SUBSTRING,
LEN,
REVERSE,
UNICODE,
LOWER,
RIGHT,
UPPER,
LTRIM,
RTRIM
Example SQL String Function -
ASCII
-
Returns the ASCII code value of a keyboard button
and the rest etc (@,R,9,*)
.
Syntax
- ASCII ( character)
SELECT ASCII('a')
-- Value = 97
SELECT ASCII('b') -- Value =
98
SELECT ASCII('c') -- Value =
99
SELECT ASCII('A') -- Value =
65
SELECT ASCII('B') -- Value =
66
SELECT ASCII('C') -- Value =
67
SELECT ASCII('1') -- Value =
49
SELECT ASCII('2') -- Value =
50
SELECT ASCII('3') -- Value =
51
SELECT ASCII('4') -- Value =
52
SELECT ASCII('5') -- Value =
53
Example SQL String Function -
SPACE
-Returns spaces in your SQL query (you can specific
the size of space).
Syntax -
SPACE ( integer)
SELECT ('SQL') + SPACE(0) + ('TUTORIALS')
-- Value = SQLTUTORIALS
SELECT ('SQL') + SPACE(1) + ('TUTORIALS')
-- Value = SQL TUTORIALS
Example SQL String Function -
CHARINDEX
-Returns the starting position of a character string.
Syntax -
CHARINDEX ( string1, string2 [ , start_location ] )
SELECT CHARINDEX('SQL', 'Well organized understand
SQL tutorial')
-- Value = 27
SELECT CHARINDEX('SQL', 'Well
organized understand SQL tutorial', 20)
-- Value = 27
SELECT CHARINDEX('SQL', 'Well organized
understand SQL tutorial', 30)
-- Value = 0
(Because the index is count from 30 and above)
Example SQL String Function -
REPLACE
-Replaces all occurrences of the
string2 in the string1 with string3.
Syntax -
REPLACE ( 'string1' , 'string2' , 'string3' )
SELECT REPLACE('All Function' , 'All', 'SQL')
-- Value =
SQL Function
Example SQL String Function -
QUOTENAME
-Returns a Unicode
string with the delimiters added to make the input string a valid Microsoft® SQL
Server™ delimited identifier.
Syntax -
QUOTENAME ( 'string' [ , 'quote_character' ] )
SELECT QUOTENAME('Sql[]String')
-- Value =
[Sql[]]String]
Example SQL String Function -
STUFF
- Deletes a specified
length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 ,
startindex , length , string2 )
SELECT STUFF('SqlTutorial', 4, 6, 'Function')
-- Value =
SqlFunctional
SELECT STUFF('GoodMorning', 5, 3, 'good')
-- Value =
Goodgoodning
Example SQL String Function -
LEFT
-Returns left part of a string with
the specified number of characters.
Syntax -
LEFT ( string , integer)
SELECT
LEFT('TravelYourself', 6)
-- Value =
Travel
SELECT
LEFT('BeautyCentury',6)
-- Value =
Beauty
Example SQL String Function -
RIGHT
-Returns right part of a string with the specified
number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT('TravelYourself', 6)
-- Value = urself
SELECT RIGHT('BeautyCentury',6)
-- Value = Century
Example SQL String Function -
REPLICATE
-Repeats string for a specified number of times.
Syntax -
REPLICATE (string,
integer)
SELECT REPLICATE('Sql', 2)
-- Value =
SqlSql
Example SQL String Function -
SUBSTRING
-Returns part of a string.
Syntax -
SUBSTRING ( string, startindex , length )
SELECT SUBSTRING('SQLServer', 4, 3)
-- Value =
Ser
Example SQL String Function -
LEN
-Returns number of characters in a
string.
Syntax -
LEN( string)
SELECT LEN('SQLServer')
-- Value = 9
Example SQL String Function -
REVERSE
-Returns reverse a string.
Syntax - REVERSE( string)
SELECT REVERSE('SQLServer')
-- Value =
revreSLQS
Example SQL String Function -
UNICODE
-Returns Unicode
standard integer value.
Syntax -
UNICODE( char)
SELECT
UNICODE('SqlServer')
-- Value = 83
(it take first character)
SELECT UNICODE('S')
-- Value = 83
Example SQL String Function -
LOWER
-Convert string to lowercase.
Syntax - LOWER( string )
SELECT LOWER('SQLServer')
-- Value =
sqlserver
Example SQL String Function -
UPPER
-Convert string to
Uppercase.
Syntax -
UPPER( string )
SELECT
UPPER('sqlserver')
-- Value =
SQLSERVER
Example SQL String Function -
LTRIM
-Returns a string after removing leading blanks on
Left side.
Syntax - LTRIM( string )
SELECT LTRIM(' sqlserver')
-- Value = 'sqlserver'
(Remove left side space or blanks)
Example SQL String Function -
RTRIM
-Returns a string after removing leading blanks on
Right side.
Syntax -
RTRIM( string )
SELECT RTRIM('SqlServer ')
-- Value =
'SqlServer' (Remove right side space or blanks)
powered by www.pHick1.com
|
|