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

--------------------------------------------------------

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

--------------------------------------------------------

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

--------------------------------------------------------

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

--------------------------------------------------------

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

--------------------------------------------------------

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

--------------------------------------------------------

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

--------------------------------------------------------

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

--------------------------------------------------------

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

--------------------------------------------------------

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

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

--------------------------------------------------------

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

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

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

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

--------------------------------------------------------

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

--------------------------------------------------------

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

--------------------------------------------------------

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

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

--------------------------------------------------------

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