Functions for positions

Functions for Manipulating Data in SQL Server

Ana Voicu

Data Engineer

Position functions

LEN()
CHARINDEX()
PATINDEX()

LEN()

Definition

Returns the number of characters of the provided string.

Syntax

LEN(character_expression)

LEN() example - constant parameter

SELECT LEN('Do you know the length of this sentence?') AS length

|length|
|------|
|40    |

LEN() example - table column parameter

SELECT DISTINCT TOP 5 
    bean_origin, 
    LEN(bean_origin) AS length
FROM ratings;

|bean_origin             |length|
|------------------------|------|
|Toscano Black           |13    |
|Trinite                 |7     |
|Ocumare- Puerto Cabello |23    |
|Maracaibo- El Rosario   |21    |
|Madagascar              |10    |

CHARINDEX()

Definition

Looks for a character expression in a given string.
Returns its starting position.

Syntax

CHARINDEX (expression_to_find, expression_to_search [, start_location])

CHARINDEX() example

SELECT 
 CHARINDEX('chocolate', 'White chocolate is not real chocolate'),
 CHARINDEX('chocolate', 'White chocolate is not real chocolate',10),
 CHARINDEX('chocolates', 'White chocolate is not real chocolate');

|position beginning|position in string|position of non-existing exp|
|------------------|------------------|----------------------------|
|7                 |29                |0                           |

PATINDEX()

Definition

Similar to CHARINDEX()
Returns the starting position of a pattern in an expression

Syntax

PATINDEX ('%pattern%', expression, [location ])

Wildcard characters

Wildcard	Explanation
%	Match any string of any length (including zero length)
_	Match on a single character
[ ]	Match on any character in the [ ] brackets (for example, [abc] would match on a, b, or c characters)

PATINDEX() example

SELECT 
  PATINDEX('%chocolate%', 'White chocolate is not real chocolate') AS position1,
  PATINDEX('%ch_c%', 'White chocolate is not real chocolate') AS position2;

|position1|position2|
|---------|---------|
|7        |7        |

Let's practice!

Functions for Manipulating Data in SQL Server