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.
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
/*
*/
