Archive

Posts Tagged ‘charindex’

SQL Functions – PATINDEX()

05.07.2011 2 comments

CHARINDEX() can only be used to search a literal string in the specified expression. In other words you cannot use wildcards. PATINDEX() provides this capability. It takes two arguments, the pattern to be searched and the expression.

— © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE @string VARCHAR(128)

SET @string =     'PATINDEX searches the string for the ' +

'first occurrence of a specified ' +

'character/String/pattern'

SELECT      PATINDEX('%s_t%', @string) AS 's t',

            PATINDEX('%pat%', @string) AS 'PATINDEX',

            PATINDEX('%pat[#k8SjZc9Dxki]%', @string) AS 'pattern',

            PATINDEX('%f[a-i]r%', @string) AS 'first'

Result Set:

s t         PATINDEX    pattern     first

———– ———– ———– ———–

17          1           87          38

 

(1 row(s) affected)

First column uses the _ Wildcard, which matches any one character, which is between 's' and 't'.

The second column uses % Wildcard, which searches for 0 or more characters. It is same as using: CHARINDEX('pat', @string).

Third column uses a #k8SjZc9Dxk Wildcard, which matches the characters not matching the specified set. Hence, ignoring the 'PATINDEX'.

Last column matches the characters specified in the range using [ ] Wildcard. Since the set specifies that only 'a' to 'i', it ignores the 'for'.

Pattern matching is based on the data collation. We can use COLLATE to enforce a case-sensitive search:

SELECT      PATINDEX('%[S]tring%', @string) AS 'string',

            PATINDEX('%[S]tring%', @string COLLATE Latin1_General_CS_AI)

            AS 'String'

Result Set:

string      String

———– ———–

23          80

 

(1 row(s) affected)

Hope This Helps!

Vishal

SQL Functions – CHARINDEX()

04.07.2011 3 comments

CHARINDEX() returns the starting position of an expression in another expression. It takes below arguments:

CHARINDEX ( exp1, exp2, position )

Where, exp1 = expression to be searched,

exp2 = the main expression which contains exp1,

position = this is optional, it specifies a position in exp2 from which the search for exp1 starts.

CHARINDEX() returns and INT/BIGINT based on the data type of exp2, which specifies the starting point of exp1. For data types declared with (MAX) it returns BIGINT.

 

— © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE @string VARCHAR(128)

SET @string =     'CHARINDEX searches for the string for the ' +

                  'first occurrence of a specified character/string'

SELECT      CHARINDEX('Char', @string)    AS 'Position',

            CHARINDEX('Char', @string, 2) AS 'Position'

Result Set:

Position    Position

———– ———–

1           75

 

(1 row(s) affected)

In the above example, the first columns returns 1 as the string started with 'CHAR…', while in the second column the search was started from 'HARINDEX…', which resulted in returning the second occurrence of 'CHAR'.

Searches are based on the collation of the data. CHARINDEX() has performed a case-insensitive search as my current collation is set to Latin1_General_CI_AI. To perform a case-sensitive search here we can apply Latin1_General_CS_AI collation to @string:

DECLARE @string VARCHAR(128)

SET @string =     'CHARINDEX searches for the string for the ' +

                  'first occurrence of a specified character/string'

SELECT      CHARINDEX('char', @string COLLATE Latin1_General_CS_AI)

            AS 'Position'

Result Set:

Position

———–

75

 

(1 row(s) affected)

In this example, the first occurrence of 'Char' is ignored as it does not match the case.

Hope This Helps!

Vishal