Shulou(Shulou.com)05/31 Report--

This article mainly explains "the related usage of SQL advanced date function". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian slowly and deeply to study and learn "the related usage of SQL advanced date function" together!

test environment

SQL Server 2017

CHARINDEX

role

searches for a character expression in the second character expression, which returns the start of the first expression (if found).

syntax

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

Note: This is a common character search function, starting with subscript 1, not 0.

If the initial subscript is added, the search proceeds from ignoring the characters preceding the initial subscript.

Example without starting value

SELECT CHARINDEX ('Data ','SQL Database Development')

results

Example of adding a starting value

SELECT CHARINDEX ('Data ','SQL Database Development', 6)

results

CONCAT_WS

role

Returns a string generated from a concatenation or concatenation of two or more string values, end-to-end. It separates the concatenated string values with the delimiter specified in the first function argument. (CONCAT_WS indicates using delimiter connections.)

syntax

CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )

Note: CONCAT_WS ignores NULL values in columns. Wrap nullable columns with the ISNULL function and provide default values.

example

SELECT CONCAT_WS ('-',' SQL','database', NULL,' development')

Results:

SOUNDEX

role

Returns a four-character code (SOUNDEX) that evaluates the similarity of two strings.

syntax

SOUNDEX ( character_expression )

Note: SOUNDEX converts an alphanumeric string into a four-character code based on the pronunciation of the string. The first character of this code is the first character of character_expression, converted to upper case. The second to fourth characters of the code are numbers representing letters in the expression. The letters A, E, I, O, U, H, W, and Y are ignored unless they are the first letters of a string. If a four-character code needs to be generated, zeros are added at the end

example

SELECT SOUNDEX ('liyue'), SOUNDEX ('liyuee')

Results:

Which means they sound very close.

DIFFERENCE

role

Returns an integer value that measures the difference between the SOUNDEX() values of two different character expressions

syntax

DIFFERENCE ( character_expression , character_expression )

Difference compares two different SOUNDEX values and returns an integer value. This value measures how well the SOUNDEX values match and ranges from 0 to 4. A value of 0 indicates weak or no similarity between SOUNDEX values;4 indicates very similar or even identical SOUNDEX values.

DIFFERENCE and SOUNDEX have collation sensitivity.

example

SELECT SOUNDEX ('liyue'), SOUNDEX ('liyuee') , DIFFERENCE('liyue', 'liyuee');

Results:

The result returned is 4, further validating our previous function.

FORMAT

role

Returns a value formatted using the specified format and optional culture. Use the FORMAT function to format date/time and numeric values into locale-identifying strings. For general data type conversion, use CAST or CONVERT.

syntax

FORMAT ( value, format [, culture ] )

note

FORMAT will return a NULL error instead of a non-valid culture. For example, NULL is returned if the value specified in format is invalid.

The FORMAT function has uncertainty.

FORMAT relies on the existence of the. NET Framework Common Language Runtime (CLR).

This function cannot be remoted because it depends on the existence of the CLR. Remoting functions that require CLR may cause errors on remote servers.

Example Date Format

DECLARE @d DATETIME = '12/18/2019'; SELECT FORMAT ( @d, ' d','en-US') AS ' US Format ' ,FORMAT ( @d, 'd','en-gb' ) AS ' UK Format ' ,FORMAT ( @d, 'd',' de-de') AS 'German Format' ,FORMAT ( @d, 'd',' zh-cn') AS 'Chinese format'; SELECT FORMAT ( @d, ' D','en-US') AS ' American format'; ,FORMAT ( @d, 'D','en-gb' ) AS ' UK Format ' ,FORMAT ( @d, 'D',' de-de') AS 'German Format' ,FORMAT ( @d, 'D',' zh-cn') AS 'Chinese format';

results

Custom Format Example

SELECT FORMAT( GETDATE(), 'dd/MM/y',' zh-cn') AS 'Custom Date' ,FORMAT(123456789,'###-##-####') AS 'Custom Number';

results

Examples of numerical formats

SELECT FORMAT(1.127456, 'N',' zh-cn') AS 'rounded format' ,FORMAT(1.123456, 'G',' zh-cn') AS 'General Format' ,FORMAT(1.123456, 'C',' zh-cn') AS 'Currency Format'

results

PATINDEX

role

Returns the starting position of the first occurrence of a pattern in a specified expression; zero if the pattern is not found in all valid text and character data types. This function is similar to CHARINDEX

syntax

PATINDEX ( '%pattern%' , expression )

Note:

PATINDEX returns NULL if pattern or expression is NULL.

PATINDEX starts at 1.

PATINDEX performs a comparison based on the collation of the input. To compare with a specified collation, you can use COLLATE to apply an explicit collation to the input.

example

SELECT PATINDEX ('% database %','SQL database development');

results

Example of using wildcards in PATINDEX

Use % and_wildcards to find the beginning of the pattern 'number'(followed by any character and 'library') in the specified string (index starts at 1)

SELECT PATINDEX ('% database %','SQL database development');

results

QUOTENAME

role

Returns a Unicode string with delimiters added to make the input string a valid SQL Server delimited identifier.

syntax

QUOTENAME ( 'character_string' [ , 'quote_character' ] )

note

'character_string'

Unicode character string of data. character_string is sysname and has a maximum of 128 characters. Input longer than 128 characters returns NULL.

'quote_character'

A single-character string used as a delimiter. Can be single quotes ('), left or right parentheses ([]), double quotes ("), left or right parentheses (()), greater than or less than sign (>

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.