String Functions in SQL SERVER

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

 

/*

*/