SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

STRING and FIND SQL Functions

By Pedro Costa,

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

SQL – STRING Function

            Context

In transact-SQL there is no easy way to operate over strings, one having frequently to work with system functions like SubString, CharIndex, Stuff, Right, Left and others, that bring unnecessary difficulty for a t-SQL developer.

            Objective

Create a funtion STRING that allows an easy and intuitive application of one or several operators over a string in similarity with what already exists in object oriented-languages and in the new interpreted languages like Python and R.

            Definition

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

            STRING (Transact-SQL)

Applies to: SQL Server (starting with 2012)

Returns a new string by applying operations over a string parameter.

            Syntax

STRING ( expression [, operator ] )

            Arguments

expression

Is a character, text or ntext expression.

For expression of different data types, converts to character.

operator

Character optional argument that defines the operations pattern to be applied to the expression

If the operator is not provided, the original expression is returned

            Operator Types

[i]                    Returns the character on the ith position

                                    i < 0: returns the character position of |i| counting backwards from the end

[i1:i2]               Returns the characters between the i1 and i2 positions

                                    i1 empty: returns all the characters up until i2

                                    i2 empty: returns all the characters starting from i1

                                    i1 and i2 empty: returns all the characters

                                    i1 or i2 < 0: counts the position of |i| backwards from the end

                                    i1 > i2: returns the characters in reverse from right to left

[o1,o2]             Returns the concatenation of the results from indexing operator o1 and o2

o1,o2                Returns the concatenation of the results from operator o1 and o2

s                      Returns the string s

            Return Types

Returns character data as:

Specified expression                                      Return Type

char/varchar/text                                            varchar

nchar/nvarchar/ntext                                      nvarchar

            Remarks

If the expression is null or empty or the result of applying the operators results in error, returns empty.

To include the ‘,’ as text, the comma should be preceded by the \ (backslash) character as ‘\,’.

The ith operator are int values. If numeric, they are rounded to int. If non-numeric, the operation is not considered.

The ith positions are 1 based.

Examples

A. Returning the 3rd and 5th character from expression

STRING(expression, '[3,5]')

B. Returning the first 2 and last 2 characters from expression

STRING(expression, '[:2],[-2:]')

C. Returning the last 3 characters in reverse order from expression

STRING(expression, '[:-3]')

D. Returning formated phone numbers as +351 ## ### ## ## from clients with format #########

Select

  STRING(PhoneNumber, '+351,[:2], ,[3:5], ,[6:7], ,[8:9]')

From

  Clients

E. Returning the last name from clients name

Select

  STRING(Name, '[' + FIND(Name, ' ', null, -1) + ':]' )

From

  Clients

            See also

FIND (Transact-SQL)


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

SQL – FIND Function

            Context

In transact-SQL it is not versatile to perform searches over strings, one being limited to use the CharIndex function or equivalents.

            Objective

Create a function FIND that allows some versatility and easiness for searching the existence and position of an expression on a string.

            Definition

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

            FIND (Transact-SQL)

Applies to: SQL Server (starting with 2008)

Searches an expression for another expression and returns its starting position if found.

            Syntax

FIND ( expression, expressionToFind [, startLocation ] [, occurrenceNumber ]  )

            Arguments

expression

Is a character, text or ntext expression to be searched.

For expression of different data types, converts to character.

expressionToFind

Sequence to be found

startLocation

int expression at which the search starts. If negative or zero, the search starts at the beginning of expressionToSearch

occurrenceNumber

int expression defining the number of the occurrence to be found.

If occurrenceNumber < 0 counts the occurrence to be found backwards from the end of the expression.

            Return Types

Returns int/bigint

            Remarks

If the expression or expressionToFind is null, returns null.

If expressionToFind is not found within expression or the solicited number of occurrence is greater then the number of occurrences found, returns 0.

The startingLocation and occurrenceNumber are 1 based.

Examples

A. Returning the starting position of ‘.’ in the expression

FIND(expression, '.')

B. Returning the position of the second occurrence of ‘.’ in the expression, starting from position 10

FIND(expression, '.', 10, 2)

C. Returning the last position of ‘.’ in the expression

FIND(expression, '.', null, -1)

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

--==========================================================================

-- Description: Examples For FIND and STRING SQL Server Functions
-- Author: Pedro Costa
-- Create date: 2018-06-05
--==========================================================================
Use AdventureWorks2012
GO

-- ====== FIND

-- A. Returning the starting position of ‘-’ in the Phone Number
Select Distinct PhoneNumber, dbo.FIND('-', PhoneNumber, null, null) As Result From Person.PersonPhone

-- B. Returning the position of ‘.’ in the Document Summary
Select DocumentSummary, dbo.FIND('.', DocumentSummary, null, 1) As Result From Production.Document Where DocumentSummary Is Not Null

-- B.2 Returning the position of ‘.’ in the Document Summary, starting from position 60
Select DocumentSummary, dbo.FIND('.', DocumentSummary, 60, 1) As Result From Production.Document Where DocumentSummary Is Not Null

-- C. Returning the last position of ‘ ’ in the Reviewer Name
Select ReviewerName, dbo.FIND(' ', ReviewerName, null, -1) From Production.ProductReview

-- ====== STRING

-- A. Returning the 3rd and 5th character from ProductNumber
Select ProductNumber, dbo.STRING(ProductNumber, '[3,5]') As Result From Production.Product

-- B. Returning the first 2 and last 2 characters from ProductNumber
Select ProductNumber, dbo.STRING(ProductNumber, '[:2],[-2:]') As Result From Production.Product

-- C. Returning the last 3 characters in reverse order from Product Number
Select ProductNumber, dbo.STRING(ProductNumber, '[:-3]') As Result From Production.Product

-- D. Returning formated Credit Card Number as ### ### ### ##-### from clients with format ##############
Select CardNumber, dbo.STRING(CardNumber, '[:3], ,[4:6], ,[7:9], ,[10,11],-,[12:14]') As Result From Sales.CreditCard

-- ====== STRING & FIND

-- E.1 Returning the First Sentence of Document Summary
Select DocumentSummary, dbo.STRING(DocumentSummary, '[:' + CAST(dbo.FIND('.', DocumentSummary, null, 1) as varchar) + ']') As Result From Production.Document Where DocumentSummary Is Not Null

-- E.1.2 Returning the First Sentence of Document Summary, starting from position 60
Select DocumentSummary, dbo.STRING(DocumentSummary, '[:' + CAST(dbo.FIND('.', DocumentSummary, 60, 1) as varchar) + ']') As Result From Production.Document Where DocumentSummary Is Not Null

-- E.2 Returning the LoginID from Employees without the Domain
Select LoginID, dbo.STRING(LoginID, '[' + CAST(dbo.FIND('\', LoginID, null, null) as varchar) + ':]') As Result From HumanResources.Employee

-- E.3 Returning the last name from Stores Name
Select [Name], dbo.FIND(' ', [Name], null, -1) As Find, dbo.STRING([Name], '[' + CAST(dbo.FIND(' ', [Name], null, -1) as varchar) + ':]') As Result From Sales.Store

-- E.4 Returning the Reviewer Name from Product Review As LastName, First Name
Select
  ReviewerName,
  Case
    When dbo.FIND(' ', ReviewerName, null, -1) != 0
      Then dbo.STRING(ReviewerName, '[' + CAST(dbo.FIND(' ', ReviewerName, null, -1) as varchar) + ':],\, ,[:' + CAST(dbo.FIND(' ', ReviewerName, null, null) - 1 as varchar) + ']')
    Else
      ReviewerName
  End As Result
From Production.ProductReview

Total article views: 583 | Views in the last 30 days: 32
 
Related Articles
SCRIPT

Find the position of all occurrences of an expression within a string

This iTVF returns both the relative and the actual positions of ALL occurrences of a string within a...

SCRIPT

Get given character's position from a string

Get given character's position from a string

SCRIPT

Find the position of Nth repeated char/string

Find the position of Nth repeated char or string

SCRIPT

Extracting string after and before a Character/Pattern

Finding characters/string before and after some character or pattern

ARTICLE

TSQL Challenge 55 - Multiply two very long decimal strings and return

This challenge to multiply two positive integer strings and return their product. The strings can be...

 
Contribute