Showing entries 11 to 20 of 1218
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: sql (reset)
How to Map MySQL’s TINYINT(1) to Boolean in jOOQ

MySQL 8 does not yet support the BOOLEAN type as specified in the SQL standard. There is a DDL “type” called BOOL, which is just an alias for TINYINT:

create table t(b bool);

select 
  table_name, 
  column_name, 
  data_type, 
  column_type
from information_schema.columns
where table_name = 't';

The above produces:

TABLE_NAME|COLUMN_NAME|DATA_TYPE|COLUMN_TYPE|
----------|-----------|---------|-----------|
t         |b          |tinyint  |tinyint(1) |

Notice that BOOL translates to a specific “type” of TINYINT, a TINYINT(1), where we might be inclined to believe that the (1) corresponds to some sort of precision, as with …

[Read more]
SQL CONCAT_WS Function Example | CONCAT_WS Method In SQL

SQL CONCAT_WS Function Example | CONCAT_WS Method In SQL is today’s topic. The SQL CONCAT_WS function is used to join two or more expressions separated by a separator. It takes up to 255 input strings which are further joined by a function. If we want to perform the join operation, CONCAT requires at least 2 strings. If it is provided with only 1 string, it will raise the error.

SQL CONCAT_WS Function

If any non-character string values are passed into the function, it will be implicitly converted to the strings before concatenating.

If NULL is added to the function, it converts it into the empty string with VARCHAR (1).

See the following syntax.

SELECT CONCAT_WS (separator, expression1, expression2, expression3…)

Parameters Separator

[Read more]
SQL DIFFERENCE Function Example | Understanding Difference Method

SQL DIFFERENCE Function Example | Understanding Difference Method is today’s topic. SQL DIFFERENCE function is used for returning the difference between the Soundex values. A Soundex value is four characters long. If two strings sound the same, then their Soundex values will be the same. If two strings sound entirely different, then none of the characters Soundex values will be the same.

SQL DIFFERENCE Function

The SQL DIFFERENCE, one of the String Function, compares two SOUNDEX values, and returns the integer.

An integer value indicates the match for the two SOUNDEX values, from 0 to 4. The 0 indicates weak or no similarity between the SOUNDEX values. 4 indicates strong similarity or identically SOUNDEX values.

For example, peace and piece sound the same so that they will have the same Soundex values.

SELECT SOUNDEX ('piece') AS Piece, SOUNDEX ('Peace') AS Peace;

See the …

[Read more]
SQL CHARINDEX Function Example

SQL CHARINDEX Function Example is today’s topic. SQL CHARINDEX function is used for returning the location of a substring of a given string. SQL CHARINDEX function is not case-sensitive. SQL CHARINDEX function searches for a substring inside a string starting from a specified location.

It returns the position of the substring found in the searched string, or zero if the substring is not found. The starting position returned is 1-based, not 0-based.

SQL CHARINDEX Function

The CHARINDEX() function searches for a substring in a string and returns the position. If the substring is not found, this function returns 0. The function performs a case-insensitive search.

See the following syntax.

SELECT CHARINDEX (substring, input_string, [start_position] )

PARAMETERS

  1. Substring: The substring that you want to find within the input_string.
[Read more]
SQL Char Function Example | Char Function In SQL Tutorial

SQL Char Function Example | Char Function In SQL Tutorial is today’s topic. SQL CHAR function is used for converting a numeric value to a character. It is just the opposite of the ASCII function. A character or string function is a function which takes one or more characters or numbers as parameters and returns the character value.

SQL Char Function

The CHAR() function returns a character based on the ASCII code.

See the following syntax.

Select CHAR (number_code)

Number_code

The number from which character is to be retrieved. An integer outside the range 0 to 255 will return a NULL character.

See the following code.

SELECT CHAR (97);

See the output.

a

Explanation

As ASCII value of a is 97 so character a is printed when number 97 was given as an input to …

[Read more]
What’s Faster? COUNT(*) or COUNT(1)?

One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there’s really no reason at all why one should be faster than the other. But is the myth justified?

Let’s measure!

How does COUNT(…) work?

But first, let’s look into some theory. The two ways to count things are not exactly the same thing. Why?

  • COUNT(*) counts all the tuples in a group
  • COUNT(<expr>) counts all the tuples in a group for which <expr> evaluates to something that IS NOT NULL

This distinction can be quite useful. Most of the time, we’ll simply COUNT(*) for convenience, but there are (at least) two cases where we don’t want that, for example:

When outer joining

[Read more]
SQL ASCII FUNCTION Example | ASCII Function In SQL

SQL ASCII FUNCTION Example | ASCII Function In SQL is today’s topic. ASCII stands for American Standard Code for Information Interchange. SQL ASCII function is used to return the numeric value of a character which is given as an input to the function. This function just acts opposite to that of CHAR function.

SQL ASCII FUNCTION Example

The ASCII function accepts a character expression and returns the ASCII code value of the leftmost character of the character expression. See the following syntax.

Syntax

SELECT ASCII (single_character or string);

PARAMETERS

  1. Single_character: It is a specified character whose numeric value will be returned.
  2. String: If a sequence of characters is inserted in function as input then only the first character, the numeric value will be returned ignoring all the …
[Read more]
SQL STUFF Function Example | Stuff Function In SQL Tutorial

SQL STUFF Function Example | Stuff Function In SQL Tutorial is today’s topic. SQL STUFF Function is used to replace the sequence of characters from the source string of given length from the new string given at the time of input to the function. It is replaced from the index which is also specified at the time of input. The STUFF() function deletes the part of the string and then inserts another part into the string, starting at the specified position.

SQL STUFF Function Example

See the syntax of stuff() function.

SELECT STUFF (source_string, start, length, add_string);

PARAMETERS

  1. source_string: It is the string that is to be processed.
  2. Start: It is an integer that is used for identifying the position to start deletion and insertion. If start is negative, zero, or longer than the length of the source_string, then the …
[Read more]
SQL TRIM Function Example | trim() Function In SQL

SQL TRIM Function Example | trim() Function In SQL is today’s topic. The SQL TRIM function is used for removing spaces and characters from both ends of the string. The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.

SQL TRIM Function Example

The syntax of TRIM() Function is following.

TRIM ([removed_characters FROM] input_string)

See the parameters of TRIM() function.

  1. removed_characters: It is a literal, variable of any non-LOB character type contains characters which will be removed.
  2. input_string: It is an expression that depicts the kind of characters to be removed.

Examples

See the following query.

Select TRIM (‘   SQL TUTORIAL …
[Read more]
SQL Substring Function Example | Substring In SQL

SQL Substring Function Example | Substring In SQL is today’s topic. SQL SUBSTRING FUNCTION is used to extract part of the string from an original string given as an input to the function. Part of the string is known as a substring. The SUBSTRING functions allow you to extract a substring from a string.

SQL Substring Function

See the syntax of SUBSTRING FUNCTION.

SELECT SUBSTRING (input_string, start_position, length);

See the following parameters.

  1. input_string: The source string from which substring will be extracted. It can be a character, binary, text, or image expression.
  2. start_position: It is an integer that specifies the location where the returned substring starts. The first character in an input_string is 1, not zero as that of in C or C++.
  3. Length: It is a positive integer that specifies the …
[Read more]
Showing entries 11 to 20 of 1218
« 10 Newer Entries | 10 Older Entries »