You are here
Home > Sql Server > Character Functions in sql

Character Functions in sql

Character Functions

  • T-SQL supports many built-in functions.

Concatenation

  • T-SQL supports two ways to concatenate strings. one with the CONCAT function and another with the plus (+) operator.
  • Example: select CONCAT(‘abc’, ‘xyz’);
  • Output: abcxyz
  • Example: select LEFT(‘abcxyz’,2) + RIGHT(‘abcxyz’,2);
  • Output: abyz

 

Substring extraction and position                               

  • T-SQL supports built-in functions like SUBSTRING, LEFT, RIGHT, CHARINDEX and PATINDEX. Using these functions, you can extract a substring from a string and identify the position of a substring within a string.

SUBSTRING:

  • This function extracts a substring from a string. It has three arguments: first argument is string, second argument is starting position and third is a length.
  • Example: select SUBSTRING(‘abcxyz’,1,4);
  • Output: abcx

LEFT:

  • It extracts a requested number of characters from the left side of the input string.
  • Example: select LEFT(‘abcxyz’,2);
  • Output: ab

RIGHT:

  • It extracts a requested number of characters from the right side of the input string.
  • Example: select RIGHT(‘abcxyz’,2);
  • Output: yz

CHARINDEX:

  • It returns the position of the first occurrence of the string provided as the first argument and within the string provided as the second argument.
  • Example: select CHARINDEX(‘ ‘, ‘abc xyz ‘);
  • Output: 4

PATINDEX:

  • It returns the location of a pattern in a string.
  • Example: select PATINDEX(‘%[0-9]%’, ‘abc12abc’);
  • Output: 4

 

String length

  • T-SQL provides LEN and DATALENGTH functions that you can use to measure the length of an input value.

LEN:

  • It returns the length of an input string in terms of the number of characters.
  • If there are any trailing spaces, it removes them.
  • Example: select LEN(‘abcd ‘);
  • Output: 4

DATALENGTH:

  • It returns the length of an input string in terms of the number of bytes.
  • It does not remove trailing spaces.
  • Example: select DATALENGTH(‘abcd ‘);
  • Output: 5

 

String alteration

  • T-SQL supports REPLACE, REPLICATE, and STUFF functions that you can use to apply alterations to an input string.

REPLACE:

  • Using this function, you can replace in an input string provided as the first argument and all occurrences of the string provided as the second argument, with the string provided as the third argument.
  • Example: select REPLACE(‘2.4.6.’, ‘.’, ‘-‘);
  • Output: 2-4-6-

REPLICATE:

  • It allows you to replicate the input string a requested number of times.
  • Example: select REPLICATE(‘8’,4);
  • Output: 8888

STUFF:

  • It operates on the input string provided as the first argument then from the character position indicated as the second argument and deletes the number of characters indicated as the third argument and then it inserts in that position the string specified as the fourth argument.
  • Example: select STUFF(‘a,b,c,d’,2,1, ‘ ‘);
  • Output: a b,c,d
  • Example: select STUFF(‘abcd’,2,3, ‘xyz’);
  • Output: axyz

 

Formatting

  • T-SQL supports number of functions that you can use to apply formatting options to an input string. Those functions are UPPER, LOWER, LTRIM, RTRIM, and FORMAT.

UPPER:

  • It converts an input string in upper case.
  • Example: select UPPER(‘abcd’);
  • Output: ABCD

LOWER:

  • It converts an input string in lower case.
  • Example: select LOWER(‘ABCD’);
  • Output: abcd

LTRIM:

  • It removes leading spaces from an input string.
  • Example: select LTRIM(‘ abcd’);
  • Output: abcd

RTRIM:

  • It removes trailing spaces from an input string.
  • Example: select RTRIM(‘abcd ‘);
  • Output: abcd

FORMAT:

  • Using FORMAT function, you can format the input value based on a .NET format string.
  • Example: select FORMAT(2468, ‘d8’);
  • Here, ‘d8’ means decimal value with 8 digits.
  • Output: 00002468

 

String splitting

  • T-SQL supports a table valued function called STRING_SPLIT.

STRING_SPLIT:

  • It accepts a string with a separated list of values provided as the first argument and a character string with the separator as the second argument and it returns result set with a column called value holding the individual split strings.
  • Example: select value from STRING_SPLIT(‘a,b,c,d’, ‘,’);
  • Output:

    value

    a

    b

    c

    d

     

 

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

Leave a Reply

Top