Search for “t” in string “Customer”, and return position:
SELECT CHARINDEX('t', 'Customer') AS MatchPosition;
The CHARINDEX() function searches for a substring in a string, and returns the position.
If the substring is not found, this function returns 0.
Note: This function performs a case-insensitive search. Continue reading SQL Server CHARINDEX Function
Return the character based on the number code 65:
SELECT CHAR(65) AS CodeToCharacter;
The CHAR() function returns the character based on the ASCII code.
CHAR(code)
SQL Server has many built-in functions.
This reference contains string, numeric, date, conversion, and some advanced functions in SQL Server.
| Function | Description |
|---|---|
| ASCII | Returns the ASCII value for the specific character |
| CHAR | Returns the character based on the ASCII code |
| CHARINDEX | Returns the position of a substring in a string |
| CONCAT | Adds two or more strings together |
| Concat with + | Adds two or more strings together |
| CONCAT_WS | Adds two or more strings together with a separator |
| DATALENGTH | Returns the number of bytes used to represent an expression |
| DIFFERENCE | Compares two SOUNDEX values, and returns an integer value |
| FORMAT | Formats a value with the specified format |
| LEFT | Extracts a number of characters from a string (starting from left) |
| LEN | Returns the length of a string |
| LOWER | Converts a string to lower-case |
| LTRIM | Removes leading spaces from a string |
| NCHAR | Returns the Unicode character based on the number code |
| PATINDEX | Returns the position of a pattern in a string |
| QUOTENAME | Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier |
| REPLACE | Replaces all occurrences of a substring within a string, with a new substring |
| REPLICATE | Repeats a string a specified number of times |
| REVERSE | Reverses a string and returns the result |
| RIGHT | Extracts a number of characters from a string (starting from right) |
| RTRIM | Removes trailing spaces from a string |
| SOUNDEX | Returns a four-character code to evaluate the similarity of two strings |
| SPACE | Returns a string of the specified number of space characters |
| STR | Returns a number as string |
| STUFF | Deletes a part of a string and then inserts another part into the string, starting at a specified position |
| SUBSTRING | Extracts some characters from a string |
| TRANSLATE | Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument. |
| TRIM | Removes leading and trailing spaces (or other specified characters) from a string |
| UNICODE | Returns the Unicode value for the first character of the input expression |
| UPPER | Converts a string to upper-case |
Return the ASCII value of the first character in “CustomerName”:
SELECT ASCII(CustomerName) AS NumCodeOfFirstChar
FROM Customers;
The ASCII() function returns the ASCII value for the specific character.
ASCII(character)
Return the current version of the MySQL database:
SELECT VERSION();
The VERSION() function returns the current version of the MySQL database, as a string.
VERSION()
Return the current user name and host name for the MySQL connection:
SELECT USER();
The USER() function returns the current user name and host name for the MySQL connection.
USER()
Return the current user name and host name for the MySQL connection:
SELECT SYSTEM_USER();
The SYSTEM_USER() function returns the current user name and host name for the MySQL connection.
SYSTEM_USER()
Return the current user name and host name for the MySQL connection:
SELECT SESSION_USER();
The SESSION_USER() function returns the current user name and host name for the MySQL connection.
SESSION_USER()
Compare two expressions:
SELECT NULLIF(25, 25);
The NULLIF() function compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned.
NULLIF(expr1, expr2)
Return the AUTO_INCREMENT id of the last row that has been inserted in a table:
SELECT LAST_INSERT_ID();
The LAST_INSERT_ID() function returns the AUTO_INCREMENT id of the last row that has been inserted in a table.
LAST_INSERT_ID(expression)