[go: up one dir, main page]
Scribd
Upload
20 views2 pages

SQL - Advance Functions

The document defines and explains the usage of various string and text manipulation functions in SQL including: - Functions to extract, concatenate, format, convert, and manipulate parts of strings like LEFT, RIGHT, CONCAT, FORMAT, CAST, LOWER, UPPER, LTRIM, etc. - Functions to search, replace, and transform strings like PATINDEX, REPLACE, TRANSLATE, and REVERSE. - Functions to generate strings or characters from numbers like CHAR, NCHAR, and UNICODE. - Examples of using REGEXP in MySQL to query for city names that do not start or end with vowels. Also shows how to count the number of columns in a table.

Uploaded by

VŨ HẢI NAM 20080169
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
20 views2 pages

SQL - Advance Functions

The document defines and explains the usage of various string and text manipulation functions in SQL including: - Functions to extract, concatenate, format, convert, and manipulate parts of strings like LEFT, RIGHT, CONCAT, FORMAT, CAST, LOWER, UPPER, LTRIM, etc. - Functions to search, replace, and transform strings like PATINDEX, REPLACE, TRANSLATE, and REVERSE. - Functions to generate strings or characters from numbers like CHAR, NCHAR, and UNICODE. - Examples of using REGEXP in MySQL to query for city names that do not start or end with vowels. Also shows how to count the number of columns in a table.

Uploaded by

VŨ HẢI NAM 20080169
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

ASCII(character) > < CHAR(code of character)

CHARINDEX(‘string_want_to_find = substring’, ‘string’, start_position)

CONCAT(string1, string2, …, string n) = string + string (‘+’ operator)

CONCAT_WS(separator, string 1, …, string n) -> use separator to concatenate strings

DATALENGTH(expression) -> count both ‘leading spaces’ & ‘trailing spaces’ = LEN(string) ->
count ‘leading spaces’ but not ‘trailing spaces’ ( if expression NULL, returns NULL)

DIFFERENCE(expression, expression) -> returns integer 0-4 (0 indicates weak or no similarity


between the SOUNDEX values. 4 indicates strong similarity or identically SOUNDEX values) > <
SOUNDEX(expression) -> returns a four-character code to evaluate the similarity of two
expressions / converts the string to a four-character code based on how the string sounds when
spoken.

FORMAT(value_to_be_formatted, format_pattern, culture) ~ CAST(expression_to_convert AS


datatype_to_convert_expression_to (length) ~ CONVERT(datatype(length), expression, style) ->
converts value (of any type) into a specified datatype NOTE: general data type conversions used
CAST() or CONVERT() while FORMAT() function to format date/time values and number values

LEFT(string_to_extract_from, no_of_chars_to_extract) > < RIGHT(string, no_of_chars)

LOWER(text) > < UPPER(text)

LTRIM(string) -> remove leading spaces from a string > < RTRIM(string) -> remove trailing
spaces from string
TRIM( [chars FROM] string) -> removes the space character OR other specified characters from
the start or end of a string. NOTE: By default, removes leading and trailing spaces from string.

NCHAR(number_code) -> return Unicode character based on number_code

PATINDEX(%pattern_in_string%, string)

QUOTENAME(string, quote_char) -> returns a Unicode string with delimiters added


(quote_char) to make valid

REPLACE(string, old_string, new_string) -> replaces all occurrences of a substring within a


string, with a new substring ~ STUFF(string, position_start_delete, length_to_be_deleted,
new_string) -> deletes a part of a string and then inserts another part into the string, starting at
a specified position NOTE:
not care about the style (uppercase or lowercase)
REPLICATE(string, no_of_time_repeated) -> repeats a string with a specified number of times

REVERSE(string)

SPACE(number) -> returns a string of specified number of space characters

STR(number, length, decimals) -> returns number as string (if number > length -> number will
be hidden by *) NOTE: default value of length = 10, of decimals = 0

SUBSTRING(string, position_start, length_to_extract) -> extracts some characters from a string

TRANSLATE(string, characters, translations) -> returns string from the first argument after the
characters specified in the 2nd argument are translated into the characters specified in the 3rd
argument NOTE: return an error if characters and translations have different lengths
Exp: SELECT TRANSLATE(‘Monday’, ‘Mon’, ‘Sun’) -> Sunday

UNICODE(character_expression) -> returns integer value (Unicode value), for the 1st character
of the input expression

MYSQL: Query the list of CITY names from STATION that do not start with vowels and do not
end with vowels. Your result cannot contain duplicates.
select distinct city from station where city not REGEXP '^[aeiou]' and city not REGEXP '[aeiou]$'

- Count number of columns in table


SELECT count(*)
FROM information_schema.columns
WHERE table_name = 'your_table_name'

You might also like