Technical Article

STRING and FIND SQL Functions

,

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

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

                    Returns the character on the ith position

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

               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

--==========================================================================
-- Description: STRING SQL Server Function
-- Author: Pedro Costa
-- Create date: 2018-06-24
-- Version: SQL Server (starting with 2012)
--==========================================================================
Use AdventureWorks2012
GO

If OBJECT_ID('dbo.STRING') Is Not Null
  Drop Function dbo.STRING
GO

Create Function dbo.STRING
(
  @pExpression Varchar(max),
  @pOperator   Varchar(max)
)
Returns Nvarchar( max )
As
Begin
  Declare @result Varchar(max) = ''

  If IsNull( @pExpression, '' ) = ''
    Return @result

  If IsNull( @pOperator, '' ) = ''
    Return @pExpression

  Declare @revExpression Varchar(max) = Reverse( @pExpression )
  Declare @lenExpression Bigint = Len( Replace(@pExpression, ' ', '.')) -- Assures The Len Expression Value Without the Automatic Trim

  Declare @CtrlComa Bit = 0
  If CharIndex( '\,', @pOperator ) > 0
  Begin
    Set @CtrlComa = 1
    Set @pOperator = Replace(@pOperator, '\,', '\#;#')
  End

  Declare @operator Varchar(max) = @pOperator + ','
  
  Declare @i Int
  Set @i = Charindex( ',', @operator )

  Declare @index Bit = 0

  Declare @j Int
  Declare @o Varchar(max)
  Declare @o1 Varchar(max),
          @o2 Varchar(max)
  Declare @j1 Int,
          @j2 Int

  While @i > 0
  Begin
    Set @o = Substring( @operator, 1, @i - 1 )

    If Substring( @o, 1, 1 ) = '['
    Begin
      Set @index = 1
      Set @o = Substring( @o, 2, Len( @o ) - 1 )
    End

    If @index = 1
    Begin
      If Substring( @o, Len( @o ), 1 ) = ']'
      Begin
        Set @index = 0
        Set @o = Substring( @o, 1, Len( @o ) - 1 )
      End

      Set @j = Charindex( ':', @o )
      If @j > 0
      Begin
        Set @o1 = Substring( @o, 1, @j - 1 )
        Set @o2 = Substring( @o, @j + 1, @i - @j - 1 )

        Set @j1 = Try_Cast( @o1 As Int )
        Set @j2 = Try_Cast( @o2 As Int )

        If @j1 Is Null
          Set @j1 = Round( Try_Cast( @o1 As Float ), 0 )

        If @j2 Is Null
          Set @j2 = Round( Try_Cast( @o2 As Float ), 0 )

        If @j2 = 0
          Set @j2 = @lenExpression

        If Not( @j1 > @j2 )
        Begin
          If @j1 < 0
            Set @j1 = @lenExpression + @j1 + 1

          If @j2 < 0
            Set @j2 = @lenExpression + @j2 + 1

          If Not( @j1 > @j2 )
            Set @result += Substring( @pExpression, @j1, @j2 - @j1 + 1 )
          Else
          Begin
            Set @result += Substring( @pExpression, @j1, @lenExpression - @j1 + 1 )
            Set @result += Substring( @pExpression, 1, @j2 )
          End
        End
        Else
        Begin
          If @j1 < 0
            Set @j1 = Abs( @j1 )

          If @j2 < 0
            Set @j2 = Abs( @j2 )

          If Not( @j1 > @j2 )
            Set @result += Substring( @revExpression, @j1, @j2 - @j1 + 1 )
          Else
          Begin
            Set @result += Substring( @revExpression, @j1, @lenExpression - @j1 + 1 )
            Set @result += Substring( @revExpression, 1, @j2 )
          End
        End
      End
      Else
      Begin
        If IsNull( @o, '' ) != ''
        Begin
          Set @j1 = Try_Cast( @o As Int )

          If @j1 Is Null
            Set @j1 = Round( Try_Cast( @o As Float ), 0 )

          If @j1 Is Not Null
          Begin
            If @j1 < 0
              Set @j1 = @lenExpression + @j1 + 1

            Set @result += Substring( @pExpression, @j1, 1 )
          End
        End
      End
    End
    Else
    Begin
      Set @result += @o
    End

    Set @operator = Substring( @operator, @i + 1, Len( @operator ) - @i )
    Set @i = Charindex( ',', @operator )
  End

  If @CtrlComa = 1
    Set @result = Replace(@result, '\#;#', ',')

  Return @result
End 
GO

--==========================================================================
-- Description: FIND SQL Server Function
-- Author: Pedro Costa
-- Create date: 2018-06-24
-- Version: SQL Server (starting with 2012)
--==========================================================================
Use AdventureWorks2012
GO

If OBJECT_ID('dbo.FIND') Is Not Null
  Drop Function dbo.FIND
GO

Create Function dbo.FIND
(
  @pExpressionToFind   Varchar(max),
  @pExpressionToSearch Varchar(max),
  @pStartLocation      Bigint = 0,
  @pOcorrenceNumber    Int = 1
)
Returns Bigint
As
Begin
  Declare @result Bigint

  If @pExpressionToFind Is Null
      Or IsNull( @pExpressionToSearch, '' ) = ''
    Return 0

  If @pStartLocation Is Null
      Or IsNull( @pStartLocation, 0 ) < 0
    Set @pStartLocation = 0

  If IsNull( @pOcorrenceNumber, 0 ) = 0
    Set @pOcorrenceNumber = 1

  If @pOcorrenceNumber < 0
  Begin
    Set @pExpressionToSearch = Reverse( @pExpressionToSearch )
    Set @pExpressionToFind = Reverse( @pExpressionToFind )
  End

  Declare @i Int = 0
  Set @result = @pStartLocation
  Declare @location Bigint = @pStartLocation
  Declare @expressionToSearch Varchar(max) = Substring( @pExpressionToSearch, @location + 1, Len( @pExpressionToSearch ) - @location )

  While @i < Abs( @pOcorrenceNumber )
  Begin
    Set @location = Charindex( @pExpressionToFind, @expressionToSearch )

    If @location = 0
      Return 0

    Set @result += @location

    Set @expressionToSearch = Substring( @expressionToSearch, @location + 1, Len( @expressionToSearch ) - @location )

    Set @i += 1
  End

  If @pOcorrenceNumber < 0
    Set @result = Len( @pExpressionToSearch ) - @result + 1 - Len( @pExpressionToFind ) + 1

  Return @result
End 
GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.67 (3)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.67 (3)

You rated this post out of 5. Change rating