In this Article, we will see about most
frequently used string functions in SQL SERVER with working examples.
Explanations and outputs are given in comment style.
The following SQL Query's and Outputs are had written and Executed with SQL
Server 2008 R2.
/*
--------------------------------------------------------
1)
ASCII - RETURN ASCII
VALUE OF THE GIVEN CHARACTER
SYNTAX:
ASCII('CHARACTER_EXPRESSION')
WHERE
'CHARACTER_EXPRESSION' - CHAR OR VARCHAR VALUE
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
DECLARE
@POSITION INT,
@STRING CHAR(15)
SET
@POSITION = 1
SET
@STRING = 'PRABHU'
WHILE
@POSITION <=
DATALENGTH(@STRING)
/* GET TOTAL COUNT OF THE STRING */
BEGIN
IF
SUBSTRING(@STRING,@POSITION,1)
<> ' '
/* GET CHAR IN GIVEN POSITION */
BEGIN
PRINT CAST(@POSITION
AS NVARCHAR)+N')'
+
N'ASCII VALUE FOR '
+
CHAR(ASCII(SUBSTRING(@STRING,@POSITION,1)))+N'
IS '
+
CAST(ASCII(SUBSTRING(@STRING,@POSITION,1))AS
NVARCHAR)
/* WILL RETURN ASCII CODE FOR GIVEN CHARACTER */
END
SET @POSITION =
@POSITION + 1 /*
INCREMENT POSITION BY 1 FOR EVERY ITERATION */
END
SET
NOCOUNT OFF
GO
/*
--------------------------------------------------------
OUTPUT IS:
--------------------------------------------------------
1)ASCII VALUE FOR P IS 80
2)ASCII VALUE FOR R IS 82
3)ASCII VALUE FOR A IS 65
4)ASCII VALUE FOR B IS 66
5)ASCII VALUE FOR H IS 72
6)ASCII VALUE FOR U IS 85
--------------------------------------------------------
*/
/*
--------------------------------------------------------
2)
CHAR - RETURN
CHARACTER FOR THE GIVEN ASCII CODE
SYNTAX:
CHAR(INTEGER_EXPRESSION)
WHERE
INTEGER_EXPRESSION - INTEGER VALUE FROM 0 TO 255. OUT OF THIS, WILL RETURN
NULL VALUE.
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
DECLARE
@POSITION INT,
@STRING CHAR(15)
SET
@POSITION = 1
SET
@STRING = 'PRABHU'
WHILE
@POSITION <=
DATALENGTH(@STRING)
/* GET TOTAL COUNT OF THE STRING */
BEGIN
IF
SUBSTRING(@STRING,@POSITION,1)
<> ' '
/* GET CHAR IN GIVEN POSITION */
BEGIN
PRINT
CAST(@POSITION
AS NVARCHAR)+N')'
+ N'ASCII VALUE FOR '
+ CHAR(ASCII(SUBSTRING(@STRING,@POSITION,1)))+N'
IS '/* WILL RETURN CHARACTER FOR GIVEN ASCII
CODE */
+
CAST(ASCII(SUBSTRING(@STRING,@POSITION,1))AS
NVARCHAR)/*
WILL RETURN ASCII CODE FOR GIVEN CHARACTER */
END
SET @POSITION =
@POSITION + 1 /*
INCREMENT POSITION BY 1 FOR EVERY ITERATION */
END
SET
NOCOUNT OFF
GO
/*
--------------------------------------------------------
OUTPUT IS:
--------------------------------------------------------
1)ASCII VALUE FOR P IS 80
2)ASCII VALUE FOR R IS 82
3)ASCII VALUE FOR A IS 65
4)ASCII VALUE FOR B IS 66
5)ASCII VALUE FOR H IS 72
6)ASCII VALUE FOR U IS 85
--------------------------------------------------------
*/
/*
--------------------------------------------------------
3)
CHARINDEX - RETURN
THE STARTING POSITION IN THE GIVEN STRING BASED ON THE EXPRESSION
SYNTAX:
CHARINDEX ('STRING_TO_BE_FOUND','STRING_TO_BE_SEARCHED_ON',STARTLOCATION)
WHERE
STRING_TO_BE_FOUND - CHARACTER OR UNICODE STRING TO BE FOUND
STRING_TO_BE_SEARCHED_ON - STRING VALUE THAT TO BE SEARCHED
STARTLOCATION - OPTINAL, START SEARCH FROM THIS INDEX ONLY
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
DECLARE
@CI_STRING VARCHAR(100)
SET @CI_STRING
= 'RETURN THE STARTING
POSITION IN THE GIVEN STRING BASED ON THE EXPRESSION'
/* SEARCH FOR EXPRESSION FROM INDEX 0(ZERO) */
PRINT
N'THE STARTING POSITION OF THE GIVEN STRING IS: '
+ CAST(CHARINDEX('THE',@CI_STRING
) AS
NVARCHAR)/*
OUTPUT IS: 8 */
/* SEARCH FOR EXPRESSION FROM SPECIFIC INDEX */
PRINT
N'THE STARTING POSITION OF THE GIVEN STRING IS: '
+ CAST(CHARINDEX('THE',@CI_STRING,32)
AS NVARCHAR)/*
OUTPUT IS: 33 */
/* SEARCH FOR EXPRESSION IN CASE SENSITIVE MODE */
PRINT
N'THE STARTING POSITION OF THE GIVEN STRING IS: '
+ CAST(CHARINDEX('The',@CI_STRING
COLLATE LATIN1_GENERAL_CS_AS)
AS NVARCHAR)/*
OUTPUT IS: 0 */
/* SEARCH FOR EXPRESSION IN CASE IN-SENSITIVE MODE */
PRINT
N'THE STARTING POSITION OF THE GIVEN STRING IS: '
+ CAST(CHARINDEX('THE',@CI_STRING
COLLATE LATIN1_GENERAL_CI_AI)
AS NVARCHAR)/*
OUTPUT IS: 8 */
SET
NOCOUNT OFF
GO
--------------------------------------------------------
/*
--------------------------------------------------------
4)
SOUNDEX - EVALUATES
SIMILARITY BETWEEN TWO OR MORE STRINGS */
/* WILL RETURN FOUR CHARACTER CODE TO FIND SIMILARITY BETWEEN TWO OR MORE
STRINGS */
--------------------------------------------------------
SET
TEXTSIZE 0
SET
NOCOUNT ON
SELECT
SOUNDEX('PRABHU'),
SOUNDEX('PRABRU'),
SOUNDEX('PRABRQ')
SET
NOCOUNT OFF
GO
--------------------------------------------------------
/*
--------------------------------------------------------
5)
DIFFERENCE - RETURN
DIFFERENCE BETWEEN TWO SOUNDEX VALUES
SYNTAX:
DIFFERENCE('CHARACTER_EXPRESSION','CHARACTER_EXPRESSION')
WHERE
'CHARACTER_EXPRESSION' - CHAR OR VARCHAR VALUE
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
SELECT
DIFFERENCE('P610',
'P610')/*
OUTPUT IS: 4 */
SET
NOCOUNT OFF
GO
--------------------------------------------------------
/*
--------------------------------------------------------
6)
LEFT - RETURN LEFT
PART OF THE GIVEN STRING WITH SPECIFIED NUMBER OF CHARACTERS
SYNTAX:
LEFT('STRING_EXPRESSION', NUMBER_OF_CHARACTERS)
WHERE
'STRING_EXPRESSION' - STRING OF UNICODE CHARACTER'S OR BINARY DATA
TYPE VALUE.
'NUMBER_OF_CHARACTERS' - NUMBER OF CHARACTERS TO BE RETURN
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
SELECT
LEFT('HELLO, GOOD
MORNING!', 5)/*
OUTPUT IS: HELLO */
SET
NOCOUNT OFF
GO
--------------------------------------------------------
/*
--------------------------------------------------------
7)
LEN - RETURNS THE
COUNT OF TOTAL NUMBER OF CHARACTERS IN THE GIVEN STRING
SYNTAX:
LEN('STRING_EXPRESSION')
WHERE
STRING_EXPRESSION - UNICODE STRING OR BINARY DATA.
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
/*RETURNS THE COUNT OF CHARACTERS IN THE STRING, EXCLUDING TRAILING BLANKS*/
/* LEADING BLANKS ALSO COUNTED BUT TRAILING BLANKS EXCLUDED */
SELECT
LEN('
RETURN LEFT PART OF THE GIVEN STRING WITH SPECIFIED NUMBER OF CHARACTERS ')
/*WILL RETURN, 81 */
/*RETURNS THE COUNT OF CHARACTERS IN THE STRING, INCLUDING TRAILING BLANKS*/
/* BOTH LEADING AND TRAILING BLANKS WILL BE COUNTED */
SELECT
DATALENGTH('
RETURN LEFT PART OF THE GIVEN STRING WITH SPECIFIED NUMBER OF CHARACTERS ')/*WILL
RETURN, 78 */
SET
NOCOUNT OFF
GO
--------------------------------------------------------
/*
--------------------------------------------------------
8)
LOWER - CONVERTS ALL
CHARACTERS IN THE STRING TO LOWER CASE LETTER
SYNTAX:
LOWER('STRING_EXPRESSION')
WHERE
'STRING_EXPRESSION' - UNICODE STRING OR BINARY DATA.
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
PRINT
N''+
LOWER('LOWER
- CONVERTS ALL CHARACTERS IN THE STRING TO LOWER CASE LETTER')
/*
OUTPUT AS:
--------------------------------------------------------
lower - converts all characters in the string to lower case letter
--------------------------------------------------------
*/
SET
NOCOUNT OFF
GO
--------------------------------------------------------
/*
--------------------------------------------------------
9)
LTRIM - RETURNS THE
GIVEN STRING AGTER REMOVING ITS LEADING BLANKS.
SYNTAX:
LTRIM('UNICODE_STRING')
WHERE
UNICODE_STRING - UNICODE DATA OR BINAY DATA
*/
--------------------------------------------------------
SET
TEXTSIZE 0
SET
NOCOUNT ON
PRINT
N''+
LTRIM('
LOWER - CONVERTS ALL CHARACTERS IN THE STRING TO LOWER CASE LETTER')/*THE
LEADING BLANKS WILL BE REMOVED*/
SET
NOCOUNT OFF
GO
--------------------------------------------------------
/*
--------------------------------------------------------
10)
UNICODE - RETURN
INTEGER VALUE, IN UNICODE STANDARD, FOR THE FIRST CHARACTER IN THE GIVEN
EXPRESSION
SYNTAX:
UNICODE('CHARACTER_VALUE')
WHERE
'CHARACTER_VALUE' - SINGLE CHARACTER OR UNICODE STRING. FOR UNICODE
STRING, THE FIRST CHARACTER ONLY BEEN TAKEN
--------------------------------------------------------
11)
NCHAR - RETURN
UNICODE CHARACTER, IN UNICODE STANDARD, FOR THE GIVEN INTEGER VALUE
SYNTAX:
NCHAR(INTEGER_VALUE)
WHERE
INTEGER_VALUE - POSITIVE INTEGER VALUE FROM 0 TO 65535. OUT OF THE
RANGE WILL RETURN NULL VALUE.
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
/* UNICODE WILL TAKE ONLY FIRST CHARACTER IN THE GIVEN STRING */
SELECT
UNICODE(N'SQL
SERVER 2008 R2')
AS UNICODE_VALUE,
NCHAR(UNICODE(N'SQL
SERVER 2008 R2'))
AS UNICODE_CHARACTER
/*
OUTPUT AS:
--------------------------------------------------------
UNICODE_VALUE | UNICODE_CHARACTER
--------------------------------------------------------
83 | S
--------------------------------------------------------
*/
SELECT
UNICODE(N'R2')AS
UNICODE_VALUE, NCHAR(UNICODE(N'R2'))
AS UNICODE_CHARACTER
/*
OUTPUT AS:
--------------------------------------------------------
UNICODE_VALUE | UNICODE_CHARACTER
--------------------------------------------------------
82 | R
--------------------------------------------------------
*/
SET
NOCOUNT OFF
GO
/* SCRIPT TO SHOW UNICODE CHARACTER AND CORRESPONDING UNICODE INTEGER VALUE FOR
THE GIVEN STRING */
--------------------------------------------------------
DECLARE
@STRINGVALUE VARCHAR(50)
SET @STRINGVALUE
= '# SCøPE #'
DECLARE
@POSITION INT
SET
@POSITION = 1
DECLARE
@STRINGCHAR CHAR
WHILE
@POSITION <=
DATALENGTH(@STRINGVALUE)
BEGIN
SET @STRINGCHAR =
SUBSTRING(@STRINGVALUE,
@POSITION , 1)
PRINT N''
+'UNICODE
VALUE FOR '
+CAST(@POSITION
AS NVARCHAR)
+'
CHARACTER ('
+NCHAR(UNICODE(@STRINGCHAR))
+')
IS: '
+CAST(UNICODE(@STRINGCHAR
) AS
NVARCHAR)
SET @POSITION =
@POSITION + 1
END
/*
OUTPUT AS:
--------------------------------------------------------
UNICODE VALUE FOR 1 CHARACTER (#) IS: 35
UNICODE VALUE FOR 2 CHARACTER ( ) IS: 32
UNICODE VALUE FOR 3 CHARACTER (S) IS: 83
UNICODE VALUE FOR 4 CHARACTER (C) IS: 67
UNICODE VALUE FOR 5 CHARACTER (ø) IS: 248
UNICODE VALUE FOR 6 CHARACTER (P) IS: 80
UNICODE VALUE FOR 7 CHARACTER (E) IS: 69
UNICODE VALUE FOR 8 CHARACTER ( ) IS: 32
UNICODE VALUE FOR 9 CHARACTER (#) IS: 35
*/
--------------------------------------------------------
/*
--------------------------------------------------------
12)
PATINDEX - RETURN THE
STARTING POSITION OF THE FIRST OCCURENSE OF A GIVEN PATTERN IN THE GIVEN STRING
*/
/* YOU CAN USE WILD CARD CHARACTERS IN THE PATTERN */
/*
SYNTAX:
PATINDEX('%PATTERN%',STRING_EXPRESSSION)
WHERE
'%PATTERN%' - CHARACTER EXPRESSION THAT TO BE FOUND; WILD CARD
CHARACTERS ARE ALLOWED.
STRING_EXPRESSSION - UNICODE STRING
*/
--------------------------------------------------------
SET
TEXTSIZE 0
SET
NOCOUNT ON
/* WITHOUT WILDCARD CHARACTERS */
SELECT
PATINDEX('%FIRST%','RETURN
THE STARTING POSITION OF THE FIRST OCCURENSE OF A GIVEN PATTERN IN THE GIVEN
STRING') /*
WILL RETURN, 37 */
/* WITH WILDCARD CHARACTERS */
SELECT
PATINDEX('%T_E%','RETURN
THE STARTING POSITION OF THE FIRST OCCURENSE OF A GIVEN PATTERN IN THE GIVEN
STRING')/* WILL
RETURN, 8 */
/* PATINDEX WITH COLLATE FOR CASE-SENSITIVE */
SELECT
PATINDEX('%t_E%','RETURN
THE STARTING POSITION OF THE FIRST OCCURENSE OF A GIVEN PATTERN IN THE GIVEN
STRING' COLLATE LATIN1_GENERAL_CS_AS)/*
WILL RETURN, 0(ZERO) */
/* PATINDEX WITH COLLATE FOR CASE-INSENSITIVE */
SELECT
PATINDEX('%p_s_t_on%','RETURN
THE STARTING POSITION OF THE FIRST OCCURENSE OF A GIVEN PATTERN IN THE GIVEN
STRING' COLLATE LATIN1_GENERAL_CI_AI)/*
WILL RETURN, 21 */
SET
NOCOUNT OFF
GO
--------------------------------------------------------
/*
--------------------------------------------------------
13)
QUOTENAME - RETURN
THE UNICODE STRING WITH DELIMITERS ADDED TO MAKE A STRING AS VALID SQL SERVER
DELIMITED IDENTIFIER */
/*
SYNTAX:
QUOTENAME('CHARACTER STRING', 'QUOTE CHARACTER')
WHERE
'CHARACTER STRING' - STRING OF UNICODE CHARACTER'S. AND IT WILL
BE MAXIMUM OF 128 CHARACTERS, HIGHER THEN THAT WILL RESULT IN RETURNING NULL
'QUOTE CHARACTER' - (OPTIONAL) ONE CHARACTER STRING TO USE AS THE
DELIMITER. IT MAY BE SINGLE(') OR DOUBLE (") QUOTAION MARK, A LEFT OR RIGHT([])
BRACKETS. IF IT NOT PROVIDED, BRACKETS WILL BE USED AS DEFAULT.
*/
--------------------------------------------------------
SET
TEXTSIZE 0
SET
NOCOUNT ON
SELECT QUOTENAME('XYZ[(@]"ABC')
AS WITHOUT_DELIMETER,
QUOTENAME('XYZ[(@]"ABC','"')
AS WITH_DELIMETER
SET
NOCOUNT OFF
GO
/*
OUTPUT AS:
--------------------------------------------------------
WITHOUT_DELIMETER | WITH_DELIMETER
--------------------------------------------------------
[XYZ[(@]]"ABC] | "XYZ[(@]""ABC"
--------------------------------------------------------
*/
/*
--------------------------------------------------------
14)
REPLACE - REPLACE ALL
OCCURENSES OF A SPECIFIED STRING WITH THE GIVEN REPLACEMENT STRING
SYNTAX:
REPLACE('STRING_TO_BE_SEARCHED', 'STRING_TO_BE_FOUND','STRING_TO_BE_REPLACED')
WHERE
'STRING_TO_BE_SEARCHED' - STRING OF UNICODE CHARACTER'S OR BINARY DATA
TYPE VALUE, THAT TO BE SEARCHED.
'STRING_TO_BE_FOUND' - STRING OF UNICODE CHARACTER'S OR BINARY DATA
TYPE VALUE THAT TO BE FOUND IN 'STRING_TO_BE_SEARCHED'.IT WILL NOT BE AN EMPTY
STRING.
'STRING_TO_BE_REPLACED' - STRING OF UNICODE CHARACTER'S OR BINARY DATA
TYPE VALUE THAT TO BE REPLACED FOR ALL OCCURENSES OF ‘STRING_TO_BE_FOUND' IN 'STRING_TO_BE_SEARCHED'
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
SELECT REPLACE('GOOD
MORNING!','MORNING','EVENING')
AS RESULT
SET
NOCOUNT OFF
GO
/*
OUTPUT AS:
--------------------------------------------------------
RESULT
--------------------------------------------------------
GOOD EVENING!
--------------------------------------------------------
*/
/*
--------------------------------------------------------
15)
REPLICATE - REPEATS A
GIVEN STRING AT GIVEN NUMBER OF TIMES.
SYNTAX:
REPLICATE('STRING_TO_BE_REPEATED', INTEGER_VALUE)
WHERE
'STRING_TO_BE_REPEATED' - STRING OF UNICODE CHARACTER'S OR BINARY DATA
TYPE VALUE.
'INTEGER_VALUE' - INTEGER VALUE THAT TO BE USED AS
NUMBER OF TIMES. IF ITS NEGATIVE VALUE IT WILL RETURN NULL.
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
SELECT REPLICATE('0',5)
AS RESULT /* WILL
REPEAT 0(ZERO) FOR FIVE TIMES*/
SELECT REPLICATE(1,5)
AS RESULT /* WILL
REPEAT 1(ONE) FOR FIVE TIMES*/
SELECT REPLICATE(2,-5)
AS RESULT /* WILL
RETURN NULL, HENCE THE NUMBER OF TIMES VALUE IS NEGATIVE */
SET
NOCOUNT OFF
GO
/*
SELECT REPLICATE(1,5) AS RESULT /* WILL REPEAT 1(ONE) FOR FIVE TIMES*/
OUTPUT AS:
--------------------------------------------------------
RESULT
--------------------------------------------------------
11111
--------------------------------------------------------
*/
/*
--------------------------------------------------------
16)
REVERSE - RETURNS THE
REVERSE OF A GIVEN STRING VALUE
SYNTAX:
REVERSE('STRING_TO_BE_REVERSED')
WHERE
'STRING_TO_BE_REVERSED' - STRING OF UNICODE CHARACTER'S OR
BINARY DATA TYPE VALUE.
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
SELECT REVERSE(NULL)
AS RESULT /* WILL
RETURN NULL */
SELECT REVERSE('SELECT')
AS RESULT /* WILL
RETURN TCELES */
SET
NOCOUNT OFF
GO
/*
--------------------------------------------------------
17)
RIGHT - RETURNS THE
RIGHT PART OF THE UNICODE CHARACTERS STRING WITH THE SPECIFIED NUMBER OF
CHARACTERS.
SYNTAX:
RIGHT('STRING_EXPRESSION', NUMBER_OF_CHARACTERS)
WHERE
'STRING_EXPRESSION' - STRING OF UNICODE CHARACTER'S OR BINARY DATA
TYPE VALUE.
'NUMBER_OF_CHARACTERS' - NUMBER OF CHARACTERS TO BE RETURN
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
SELECT RIGHT('SELECT',-2)
AS RESULT /* ERROR
WILL OCCURD, HENCE NUMBER OF CHARACTERS SHOULD NOT BE NEGATIVE */
SELECT RIGHT('SELECT',2)
AS RESULT /* WILL
RETURN CT */
SET
NOCOUNT OFF
GO
/*
*/
/*
--------------------------------------------------------
18)
RTRIM - REMOVES ALL
TRAILING BLANKS IN THE GIVEN STRING.
SYNTAX:
RTRIM('STRING_EXPRESSION')
WHERE
'STRING_EXPRESSION' - STRING OF UNICODE CHARACTER'S OR
BINARY DATA TYPE VALUE.
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
SELECT RTRIM(NULL)
AS RESULT /* WILL
RETURN NULL */
SELECT RTRIM('SELECT
') AS
RESULT /* WILL RETURN SELECT */
SET
NOCOUNT OFF
GO
/*
*/
/*
--------------------------------------------------------
19)
SPACE - RETURNS A
STRING OF REPEATED SPACES.
SYNTAX:
SPACE(NON_NEGATIVE_INTEGER_VALUE)
WHERE
'NON_NEGATIVE_INTEGER_VALUE' - INTEGER VALUE SHOWING THAT
NUMBER OF SPACES. IF ITS NEGATIVE OR NULL, WILL RETURN NULL
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
DECLARE @FIRSTNAME
VARCHAR(10)
= 'PRABHU'
DECLARE @LASTNAME
VARCHAR(10)
= 'RAJA'
SELECT @FIRSTNAME +
SPACE(3)
+ @LASTNAME AS
PERSON_NAME
SET
NOCOUNT OFF
GO
/*
OUTPUT AS:
--------------------------------------------------------
PERSON_NAME
--------------------------------------------------------
PRABHU RAJA
--------------------------------------------------------
*/
/*
--------------------------------------------------------
20)
SUBSTRING - RETURNS A
PART OF A GIVEN STRING.
SYNTAX:
SUBSTRING('STRING_EXPRESSION',START_EXPRESSION,LENGTH_EXPRESSION)
WHERE
'STRING_EXPRESSION' - CHARACTER, UNICODE STRING, BINARY OR IMAGE.
START_EXPRESSION - STARTING INDEX IN GIVEN STRING_EXPRESSION.
LENGTH_EXPRESSION - LENGTH OF STRING TO BE RETURNED FROM ORIGINAL
STRING.
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
DECLARE @FIRSTNAME
VARCHAR(6)
= 'PRABHU'
DECLARE @LASTNAME
VARCHAR(4)
= 'RAJA'
SELECT SUBSTRING(@FIRSTNAME
+ @LASTNAME,2,4)
AS PERSON_NAME /* WILL
RETURN, RABH */
SELECT SUBSTRING(@FIRSTNAME
+ @LASTNAME,-2,7)
AS PERSON_NAME /* WILL
RETURN, PRAB */
SET
NOCOUNT OFF
GO
/*
*/
/*
--------------------------------------------------------
21)
UPPER - RETURNS A
GIVEN CHARACTER STRING AFTER REPLACING ALL LOWER-CASE CHARACTERS TO UPPER-CASE
CHARACTERS.
SYNTAX:
UPPER('STRING_EXPRESSION')
WHERE
'STRING_EXPRESSION' - UNICODE STRING OR BINARY DATA.
--------------------------------------------------------
*/
SET
TEXTSIZE 0
SET
NOCOUNT ON
DECLARE @FIRSTNAME
VARCHAR(6)
= 'prabhu'
DECLARE @LASTNAME
VARCHAR(4)
= 'raja'
SELECT UPPER(@FIRSTNAME
+SPACE(1)+
@LASTNAME) AS
PERSON_NAME /* WILL RETURN, PRABHU RAJA */
SET
NOCOUNT OFF
GO
/*
*/