Scribd Logo
Upload

Welcome to Scribd!

  • 53 views

    Mysql Builtin Func

    Uploaded by

    Amol Adhangale
    The document discusses the MySQL CONCAT and CONCAT_WS functions which are used to concatenate string values. CONCAT concatenates two or more strings into a single string, while CONCAT_WS concatenates strings with a separator. Some key points made are: CONCAT requires at least one parameter, CONCAT_WS skips NULL values after the separator, and both functions return NULL if any argument is NULL. Examples of using each function are provided.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Mysql Builtin Func

    Uploaded by

    Amol Adhangale
    53 views21 pages
    The document discusses the MySQL CONCAT and CONCAT_WS functions which are used to concatenate string values. CONCAT concatenates two or more strings into a single string, while CONCAT_WS concatenates strings with a separator. Some key points made are: CONCAT requires at least one parameter, CONCAT_WS skips NULL values after the separator, and both functions return NULL if any argument is NULL. Examples of using each function are provided.

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses the MySQL CONCAT and CONCAT_WS functions which are used to concatenate string values. CONCAT concatenates two or more strings into a single string, while CONCAT_WS concatenates strings with a separator. Some key points made are: CONCAT requires at least one parameter, CONCAT_WS skips NULL values after the separator, and both functions return NULL if any argument is NULL. Examples of using each function are provided.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    53 views21 pages

    Mysql Builtin Func

    Uploaded by

    Amol Adhangale
    The document discusses the MySQL CONCAT and CONCAT_WS functions which are used to concatenate string values. CONCAT concatenates two or more strings into a single string, while CONCAT_WS concatenates strings with a separator. Some key points made are: CONCAT requires at least one parameter, CONCAT_WS skips NULL values after the separator, and both functions return NULL if any argument is NULL. Examples of using each function are provided.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 21

    MySQL CONCAT Function

    To concatenate two or more quoted


    string values, you place the string
    next to each other as the following
    syntax:
    SELECT 'MySQL ' 'String '
    'Concatenation';

    MySQL string concatenation is


    cleaner in comparison with other
    database management systems.
    Besides using spaces for string
    concatenation, MySQL provides two
    other functions that concatenate
    string values: CONCAT and
    CONCAT_WS.
    The MySQL CONCAT function takes
    one or more string arguments and
    concatenates them into a single
    string. The CONCAT function
    requires a minimum of one parameter
    otherwise it raises an error.
    The following illustrates the syntax of
    the CONCAT function.
    CONCAT(string1,string2, ...
    );
    The CONCAT function converts all
    arguments to the string type before
    concatenating. If any argument is
    NULL, the CONCAT function returns
    a NULL value.
    The following statement
    concatenates two quoted strings:
    MySQL and CONCAT.
    SELECT
    CONCAT('MySQL','CONCAT');

    If you add a NULL value, the


    CONCAT function returns a NULL
    value as follows:
    SELECT
    CONCAT('MySQL',NULL,'CONCAT
    ');
    To get the full names of contacts, you
    use the CONCAT function to
    concatenate first name, space, last
    name as the following statement:
    SELECT
    concat(contactFirstName,'
    ',contactLastName) Fullname
    FROM
    customers;
    MySQL provides a special form of the
    CONCAT function: CONCAT_WS
    function. The CONCAT_WS function
    concatenates two or more string
    values with a predefined separator.
    The following illustrates the syntax of
    the CONCAT_WS function:
    CONCAT_WS(seperator,string1
    ,string2, ... );
    The first argument is the separator
    for other arguments: string1, string2,
    …
    The CONCAT_WS function adds the
    separator between string arguments
    and returns a single string with the
    separator inserted between string
    arguments.
    The following statement
    concatenates two string values: John
    and Doe, and separates these two
    strings by a comma:
    SELECT
    CONCAT_WS(',','John','Doe')
    ;

    The CONCAT_WS function returns


    NULL if and only if the first argument,
    which is the separator, is NULL. See
    the following example:
    SELECT
    CONCAT_WS(NULL ,'Jonathan',
    'Smith');

    Unlike the CONCAT function, the


    CONCAT_WS function skips NULL
    values after the separator argument.
    In other words, it ignores NULL
    values.
    SELECT
    CONCAT_WS(',','Jonathan',
    'Smith',NULL);

    The following statement constructs


    complete addresses using the
    CONCAT_WS function:
    SELECT
    CONCAT_WS(CHAR(13),
    CONCAT_WS(' ',
    contactLastname,
    contactFirstname),
    addressLine1,
    addressLine2,
    CONCAT_WS(' ',
    postalCode, city),
    country,
    CONCAT_WS(CHAR(13),
    '')) AS Customer_Address
    FROM
    customers;
    Here is the output result:
    Customer_Address
    Schmitt Carine
    54, rue Royale
    44000 Nantes
    France
    King Jean
    8489 Strong St.
    83030 Las Vegas
    USA
    ...
    This example uses the
    SUBSTRING_INDEX() function to
    extract the house numbers from the
    addresses for all customers in the
    USA:
    SELECT customerName,
    addressLine1,
    SUBSTRING_INDEX(addressLine
    1, ' ', 1) house_noFROM
    customers
    WHERE country = 'USA'
    ORDER BY
    customerName;

    The following picture shows the


    partial output:
    The following statement finds
    whether the quantity of products
    which the customer ordered is odd or
    even.
    SELECT
    orderNumber,
    SUM(quantityOrdered)
    Qty,
    IF(MOD(SUM(quantityOrdered)
    ,2),
    'Odd',
    'Even') oddOrEven
    FROM
    orderdetails
    GROUP BY
    orderNumber
    ORDER BY orderNumber;
    In this example:
    First, we used the SUM() function to
    get the total quantity of products by
    sales order.
    Then, we used the MOD() function to
    find the remainder of the total
    quantity divided by two. This results
    in zero or one, depending on where
    the total quantity is even or odd.
    Finally, we used the IF() function to
    display the Odd and Even string
    based on the result of the MOD()
    function.
    Here is the output:
    MySQL provides many useful date
    functions that allow you to manipulate
    date effectively.
    To get the current date and time, you
    use NOW() function.
    SELECT NOW();
    | NOW() |
    | 2017-05-13 07:59:38 |
    1 row in set (0.02 sec)
    To get only date part of a DATETIME
    value, you use the DATE() function.
    SELECT DATE(NOW());
    | DATE(NOW()) |
    | 2015-07-13 |
    1 row in set (0.01 sec)
    To get the current system date, you
    use CURDATE() function as follows:
    SELECT CURDATE();
    | CURDATE() |
    | 2015-07-13 |
    1 row in set (0.02 sec)
    To format a date value, you use
    DATE_FORMAT function. The
    following statement formats the date
    asmm/dd/yyyy using the date format
    pattern %m/%d/%Y :
    SELECT
    DATE_FORMAT(CURDATE(),
    '%m/%d/%Y') today;
    | today |
    | 07/13/2015 |
    1 row in set (0.02 sec)
    To calculate the number of days
    between two date values, you use
    the DATEDIFF function as follows:
    SELECT DATEDIFF('2015-11-
    04','2014-11-04') days;
    | days |
    | 365 |
    1 row in set (0.02 sec)
    To add a number of days, weeks,
    months, years, etc., to a date value,
    you use the DATE_ADD function:
    SELECT
    '2015-01-01' start,
    DATE_ADD('2015-01-01',
    INTERVAL 1 DAY) 'one day
    later',
    DATE_ADD('2015-01-01',
    INTERVAL 1 WEEK) 'one week
    later',
    DATE_ADD('2015-01-01',
    INTERVAL 1 MONTH) 'one
    month later',
    DATE_ADD('2015-01-01',
    INTERVAL 1 YEAR) 'one year
    later';

    Similarly, you can subtract an interval


    from a date using the DATE_SUB
    function:
    SELECT
    '2015-01-01' start,
    DATE_SUB('2015-01-01',
    INTERVAL 1 DAY) 'one day
    before',
    DATE_SUB('2015-01-01',
    INTERVAL 1 WEEK) 'one week
    before',
    DATE_SUB('2015-01-01',
    INTERVAL 1 MONTH) 'one
    month before',
    DATE_SUB('2015-01-01',
    INTERVAL 1 YEAR) 'one year
    before';

    If you want to get the day, month,


    quarter, and year of a date value, you
    can use the corresponding function
    DAY, MONTH, QUARTER, and
    YEAR as follows:
    SELECT DAY('2000-12-31')
    day,
    MONTH('2000-12-31')
    month,
    QUARTER('2000-12-
    31') quarter,
    YEAR('2000-12-31')
    year;
    | day | month | quarter |
    year |
    | 31 | 12 | 4 |
    2000 |
    1 row in set (0.00 sec)
    To get the week information week
    related functions. For example,
    WEEK function returns the week
    number, WEEKDAY function returns
    the weekday index, and
    WEEKOFYEAR function returns the
    calendar week.
    SELECT WEEKDAY('2000-12-
    31') weekday,
    WEEK('2000-12-31') week,
    WEEKOFYEAR('2000-12-
    31') weekofyear;
    | weekday | week |
    weekofyear |
    | 6 | 53 |
    52 |
    1 row in set (0.04 sec)

    The week function returns the week


    number with the zero-based index if
    you don’t pass the second argument
    or if you pass 0. If you pass 1, it will
    return week number with 1-indexed.
    SELECT WEEKDAY('2000-12-
    31') weekday, WEEK('2000-
    12-31',1) week,
    WEEKOFYEAR('2000-12-31')
    weekofyear;
    | weekday | week |
    weekofyear |
    | 6 | 52 |
    52 |
    1 row in set (0.00 sec)

    You might also like