----------------------------------------------------------------------------------------------------------------------------------
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
More »
