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)
The FROM command is used to specify which table to select or delete data from.
The following SQL statement selects the “CustomerName” and “City” columns from the “Customers” table:
SELECT CustomerName, City FROM Customers;
The following SQL statement selects all the columns from the “Customers” table: Continue reading SQL FROM Keyword
The FOREIGN KEY constraint is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
The EXISTS command tests for the existence of any record in a subquery, and returns true if the subquery returns one or more records.
The following SQL lists the suppliers with a product price less than 20:
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);
The following SQL lists the suppliers with a product price equal to 22: Continue reading SQL EXISTS Keyword
The EXEC command is used to execute a stored procedure.
The following SQL executes a stored procedure named “SelectAllCustomers”:
EXEC SelectAllCustomers;
The DROP VIEW command deletes a view.
The following SQL drops the “Brazil Customers” view:
DROP VIEW [Brazil Customers];
The DROP TABLE command deletes a table in the database.
The following SQL deletes the table “Shippers”:
DROP TABLE Shippers;
Note: Be careful before deleting a table. Deleting a table results in loss of all information stored in the table!