Tutorial by Examples

Trim is used to remove write-space at the beginning or end of selection In MSSQL there is no single TRIM() SELECT LTRIM(' Hello ') --returns 'Hello ' SELECT RTRIM(' Hello ') --returns ' Hello' SELECT LTRIM(RTRIM(' Hello ')) --returns 'Hello' MySql and Oracle SELECT TRIM(' Hello ') ...

In (standard ANSI/ISO) SQL, the operator for string concatenation is ||. This syntax is supported by all major databases except SQL Server: SELECT 'Hello' || 'World' || '!'; --returns HelloWorld! Many databases support a CONCAT function to join strings: SELECT CONCAT('Hello', 'World'); --retur...

SELECT UPPER('HelloWorld') --returns 'HELLOWORLD' SELECT LOWER('HelloWorld') --returns 'helloworld'

Syntax is: SUBSTRING ( string_expression, start, length ). Note that SQL strings are 1-indexed. SELECT SUBSTRING('Hello', 1, 2) --returns 'He' SELECT SUBSTRING('Hello', 3, 3) --returns 'llo' This is often used in conjunction with the LEN() function to get the last n characters of a string of un...

Splits a string expression using a character separator. Note that STRING_SPLIT() is a table-valued function. SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' '); Result: value ----- Lorem ipsum dolor sit amet.

Stuff a string into another, replacing 0 or more characters at a certain position. Note: start position is 1-indexed (you start indexing at 1, not 0). Syntax: STUFF ( character_expression , start , length , replaceWith_expression ) Example: SELECT STUFF('FooBarBaz', 4, 3, 'Hello') --returns...

SQL Server The LEN doesn't count the trailing space. SELECT LEN('Hello') -- returns 5 SELECT LEN('Hello '); -- returns 5 The DATALENGTH counts the trailing space. SELECT DATALENGTH('Hello') -- returns 5 SELECT DATALENGTH('Hello '); -- returns 6 It should be noted though, that DATALE...

Syntax: REPLACE( String to search , String to search for and replace , String to place into the original string ) Example: SELECT REPLACE( 'Peter Steve Tom', 'Steve', 'Billy' ) --Return Values: Peter Billy Tom

Syntax is: LEFT ( string-expression , integer ) RIGHT ( string-expression , integer ) SELECT LEFT('Hello',2) --return He SELECT RIGHT('Hello',2) --return lo Oracle SQL doesn't have LEFT and RIGHT functions. They can be emulated with SUBSTR and LENGTH.SUBSTR ( string-expression, 1, intege...

Syntax is: REVERSE ( string-expression ) SELECT REVERSE('Hello') --returns olleH

The REPLICATE function concatenates a string with itself a specified number of times. Syntax is: REPLICATE ( string-expression , integer ) SELECT REPLICATE ('Hello',4) --returns 'HelloHelloHelloHello'

MySQL3.19 Checks if a string matches a regular expression (defined by another string). SELECT 'bedded' REGEXP '[a-f]' -- returns True SELECT 'beam' REGEXP '[a-f]' -- returns False

The Replace function in SQL is used to update the content of a string. The function call is REPLACE( ) for MySQL, Oracle, and SQL Server. The syntax of the Replace function is: REPLACE (str, find, repl) The following example replaces occurrences of South with Southern in Employees table: FirstN...

DATABASE : SQL Server PARSENAME function returns the specific part of given string(object name). object name may contains string like object name,owner name, database name and server name. More details MSDN:PARSENAME Syntax PARSENAME('NameOfStringToParse',PartIndex) Example To get object nam...

Return the index of the first occurrence of a substring (zero if not found) Syntax: INSTR ( string, substring ) SELECT INSTR('FooBarBar', 'Bar') -- return 4 SELECT INSTR('FooBarBar', 'Xar') -- return 0