SQL Server: PATINDEX String Functions in SQL Server


PATINDEX: The PATINDEX functions return the starting position of a pattern you specify.

You must include percent signs before and after the pattern,

Unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column or text.

Syntax:

PATINDEX ( '%pattern%', expression )

Pattern: Pattern is a character expression that contains a sequence of the characters. Wildcard characters can be used.

Expression: The expression that is searched for the specified pattern.

Example:

SELECT PATINDEX('%e____le%', 'This example of the Patindex');



Note: PATINDEX works just like LIKE, so you can use any of the wildcards. You do not have to enclose the pattern between percents. PATINDEX('a%', 'abc') returns 1 and PATINDEX('%a', 'cba') returns 3.

Unlike LIKE, PATINDEX returns a position, similar to what CHARINDEX does.

Query to select text only from alphanumeric string in SQL Server


If you want to select text only from alphanumeric string use following query


DECLARE @str VARCHAR(400)
DECLARE @expres  VARCHAR(50) = '%[0-9]%'
SET @str = 'Dilip 007 Singh'
WHILE PATINDEX( @expres, @str ) > 0
BEGIN
       SET @str = REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),'')
 END
SELECT @str


Result:
TEXT ONLY
-------------
Dilip Kumar

Same as this, if you want to select numeric value only from the alphanumeric string, use the below query.


DECLARE @str VARCHAR(400)
DECLARE @expres  VARCHAR(50) = '%[a-z]%'
SET @str = 'Dilip 007 Singh'
WHILE PATINDEX( @expres, @str ) > 0
BEGIN
       SET @str =REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),'')
END
SELECT @str AS NUMERICONLY


Result:
NUMERIC ONLY
--------------------

 007

SQL Server: RIGHT String Functions in SQL Server


RIGHT: This function use when we want to select a Right part of the character string with the specified number of characters.

Syntax:

RIGHT ( character_expression , integer_expression )

character_expression: Character expression is the expression of character or binary data, except text or ntext, character_expression can be of any data type.

integer_expression: integer_expression is a positive integer that specifies how many characters of the character_expression will return.

For example: 
DECLARE @str VARCHAR(60) 
SET @str='dilip Kumar Singh' 
select RIGHT(@str,5) AS RightFiveChar 
Result:

RightFiveChar
-------------
Singh
(1 row(s) affected)


SQL Server: LEFT String Functions in SQL Server


Left: This function use when we want to select a left part of the character string with the specified number of characters.

Syntax:

LEFT ( character_expression , integer_expression )

character_expression: Character expression is the expression of character or binary data, except text or ntext, character_expression can be of any data type.

integer_expression: integer_expression is a positive integer that specifies how many characters of the character_expression will be returned.

For example:

DECLARE @str VARCHAR(60)  

SET @str='dilip Kumar Singh'

select Left(@str,5) AS LeftFiveChar



Result:

LeftFiveChar

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

dilip


(1 row(s) affected)

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...