Microsoft sql server like function

The escape character has no default value and must be evaluated to only one character. See the following customers table from the sample database :.

The following example returns the customers whose last name ends with the string er :. The following statement retrieves the customers whose last name starts with the letter t and ends with the letter s :. For more information, see Execute User-defined Functions. Functions have the following properties.

The values of these properties determine whether functions can be used in computed columns that can be persisted or indexed.

The data access and determinism properties of CLR functions can be specified by the user. A computed column that invokes a user-defined function can be used in an index when the user-defined function has the following property values:. For more information, see Indexes on Computed Columns.

The extended stored procedure, when it is called from inside a function, cannot return result sets to the client.

The extended stored procedure could connect back to an instance of SQL Server; however, it should not try to join the same transaction as the function that invoked the extended stored procedure. Similar to invocations from a batch or stored procedure, the extended stored procedure will be executed in the context of the Windows security account under which SQL Server is running. The following Service Broker statements cannot be included in the definition of a Transact-SQL user-defined function:.

User-defined functions can be nested; that is, one user-defined function can call another. The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. User-defined functions can be nested up to 32 levels. Exceeding the maximum levels of nesting causes the whole calling function chain to fail.

Any reference to managed code from a Transact-SQL user-defined function counts as one level against the level nesting limit. Methods invoked from within managed code do not count against this limit.

You must ensure that results are always ordered in the specified order. If the results are not in the specified order, SQL Server will generate an error message when the query is executed.

If an ORDER clause is specified, the output of the table-valued function must be sorted according to the collation of the column explicit or implicit. For example, if the column collation is Chinese either specified in the DDL for the table-valued function or obtained from the database collation , the returned results must be sorted according to Chinese sorting rules.

The ORDER clause, if specified, is always verified by SQL Server while returning results, whether or not it is used by the query processor to perform further optimizations. See sys. The following table lists the system catalog views that you can use to return metadata about user-defined functions. The following example creates the user-defined function ISOweek. This function takes a date argument and calculates the ISO week number. In the example, the option CALLER specifies that the procedure will be executed in the context of the user that calls it.

The following example returns an inline table-valued function in the AdventureWorks database. In functions, such errors cause the execution of the function to stop. This in turn causes the statement that invoked the function to be canceled. END block cannot have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table.

The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.

However, SQL Server does not execute the function when it is invoked. The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.

For more information and performance considerations on user-defined functions, see Create User-defined Functions Database Engine. Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables. Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

The following nondeterministic built-in functions can be used in Transact-SQL user-defined functions. The following nondeterministic built-in functions cannot be used in Transact-SQL user-defined functions.

Evaldas Buinauskas Evaldas Buinauskas 13k 11 11 gold badges 48 48 silver badges 91 91 bronze badges. Lukasz Szozda Lukasz Szozda k 18 18 gold badges silver badges bronze badges. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog.

Podcast Making Agile work for data science.