Trending December 2023 # How To Create Table In Hive With Query Examples? # Suggested January 2024 # Top 20 Popular

You are reading the article How To Create Table In Hive With Query Examples? updated in December 2023 on the website Katfastfood.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 How To Create Table In Hive With Query Examples?

Introduction to Hive Table

In the hive, the tables consist of columns and rows and store the related data in the table format within the same database. The table is storing the records or data in tabular format. The tables are broadly classified into two parts, i.e., external table and internal table.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The default storage location of the Table varies from the hive version. From HDP 3.0, we are using hive version 3.0 and more. The default Table location was changed from HDP 3.0 version / Hive version 3.0. The location for the external hive Table is “/warehouse/tablespace/external/hive/” and the location for the manage Table is “/warehouse/tablespace/managed/hive”.

In the older version of the hive, the default storage location of the hive Table is “/apps/hive/warehouse/”.

Syntax

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [ database name ] table name [ ROW FORMAT row format] [ STORED AS file format] How to create a Table in Hive?

Hive internal table

Hive external table

Note: We have the hive “hql” file concept with the help of “hql” files, we can directly write the entire internal or external table DDL and directly load the data in the respective table.

1. Internal Table

The internal table is also called a managed table and is owned by a “hive” only. Whenever we create the table without specifying the keyword “external” then the tables will create in the default location.

If we drop the internal or manage the table, the table DDL, metadata information, and table data will be lost. The table data is available on HDFS it will also lose. We should be very careful while dropping any internal or managing the table.

DDL Code for Internal Table

create table emp.customer ( idint, first_name string, last_name string, gender string, company_name string, job_title string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' lines terminated by 'n' location "/emp/table1" tblproperties ("skip.header.line.count"="1");

Note: To load the data in hive internal or manage the table. We are using the “location” keyword in DDL Code. From the same location, we have kept the CSV file and load the CSV file data in the table.

Output:

2. External Table

The best practice is to create an external table. Many organizations are following the same practice to create tables. It does not manage the data of the external table, and the table is not created in the warehouse directory. We can store the external table data anywhere on the HDFS level.

The external tables have the facility to recover the data, i.e., if we delete/drop the external table. Still no impact on the external table data present on the HDFS. It will only drop the metadata associated with the table.

DDL Code for External Table

create external table emp.sales ( idint, first_name string, last_name string, gender string, email_id string, city string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' lines terminated by 'n' location "/emp/sales" tblproperties ("skip.header.line.count"="1");

Note: we can directly store the external table data on the cloud or any other remote machine in the network. It will depend on the requirement.

Output:

How to modify/alter the Table?

Here we have the facility to alter or modify the existing attributes of the Table. With the help of the “alter” functionality, we can change the column name, add the column, drop the column, change the column name, and replace the column.

We can alter the below Table attributes.

1. Alter/ rename the tablename

Syntax:

ALTER TABLE [current table name] RENAME TO [new table name]

Query to Alter Table Name :

ALTER TABLE customer RENAME TO cust;

Output:

Before alter

After alter

2. Alter/ add column in the table

Syntax:

ALTER TABLE [current table name] ADD COLUMNS (column spec[, col_spec ...])

Query to add Column :

ALTER TABLE cust ADD COLUMNS (dept STRING COMMENT 'Department');

Output:

Sample view of the table

We are adding a new column in the table “department = dept”

3. Alter/change the column name

Syntax:

ALTER TABLE [current table name] CHANGE [column name][new name][new type]

Query to change column name :

ALTER TABLE cust CHANGE first_name name string;

Output:

Sample view of the customer table.

How to drop the Table?

Drop Internal or External Table

Syntax:

DROP TABLE [IF EXISTS] table name;

Drop Query:

drop table cust;

Output:

Before drop query run

After dropping the query, run on the “cust” table.

Conclusion

We have seen the uncut concept of “Hive Table” with the proper example, explanation, syntax, and SQL Query with different outputs. The table is useful for storing the structure data. The table data is helpful for various analysis purposes like BI, reporting, helpful/easy in data slicing and dicing, etc. The internal table is managed, and the hive does not manage the external table. We can choose the table type we need to create per the requirement.

Recommended Articles

We hope that this EDUCBA information on “Hive Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

You're reading How To Create Table In Hive With Query Examples?

How To Create Hyperlink In Excel Vba With Examples?

Definition of VBA Hyperlink

The hyperlink is commonly used with websites for navigating from one page to another or one website to another on the internet. In a similar way, we can control the movements within excel worksheet too. The different operations that can be performed in Excel are:

Moving to a specific location within the current workbook.

Opening different documents and select a mentioned area within the document.

Navigating to webpages from the worksheet.

Sending email to a defined address.

The hyperlink is easy to recognize because of its color change, mostly in blue. There exist different methods to create a hyperlink in excel and let using VBA.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Create a Hyperlink in Excel Using VBA Code?

You can add a hyperlink to a text or one sheet to another worksheet within excel using hyperlink add property. The format needs to be followed by specifying where the hyperlink should be created and navigation URL etc.

Format for VBA Hyperlink Add

The format shows the parameters need to be provided to add a hyperlink to a worksheet.

Anchor: Defines the cell you want to create the hyperlink.

Address: The URL to which the navigation should move.

[SubAddress]: Subaddress of the URL.

[ScreenTip]: The mouse pointer value to be showed while placing a mouse pointer.

[Text to Display]: The text needs to be displayed on the cell.

Use the Active cell property to add a hyperlink.

Examples to Create Hyperlinks in Excel VBA

Below are the different examples to create hyperlinks in excel using VBA code.

You can download this VBA Hyperlink Excel Template here – VBA Hyperlink Excel Template

Example #1 – Creating a hyperlink from the Worksheet to a website

We want to create a hyperlink from worksheet named sub to a website using VBA code in excel.

Below are the steps to create a hyperlink in Excel VBA:

Step 1: Create a function named hyper to add the hyperlink.

Code:

Private Sub

hyper()

End Sub

Step 2: Use the Active cell object to get open the hyperlink add method.

Code:

Private Sub

hyper() ActiveCell.Hyperlinks.Add(

End Sub

Step 3: Provide the parameter values to the hyperlink add method.

Code:

Private Sub

hyper()

End Sub

Anchor: name of the worksheet

Address: Hyperlink to where the control to be navigated, given the website address

ScreenTip: The mouse pointer text

TextToDisplay: To which text the hyperlink is to be assigned

Step 4: Hit F5 or Run button under VBE to run this code and see the output.

Example #2 – Hyperlink to Connect Two Worksheets

We have two worksheets named Home and sub. Let’s try to create a hyperlink from sub to home using VBA code.

Follow the below steps to create a hyperlink from one worksheet to another within the same workbook using the VBA code.

Step 1: Create a function, where we will write all codes to perform the action. Write code to select the worksheet ‘sub’ using the selection method of the worksheet.

Code:

Private Sub

hyper1() Worksheets("sub").Select

End Sub

Since the control moves within the sheet, it is necessary to select the worksheet in which you are creating the hyperlink.

Step 2: Select the cell range within the sheet where the hyperlink is want to create.

Code:

Private Sub

hyper1() Worksheets("sub").Select Range("A1").Select

End Sub

Step 3: Now let’s add the hyperlink using the active cell property.

Code:

Private Sub

hyper1() Worksheets("sub").Select Range("A1").Select

End Sub

Since the worksheet is already selected, Anchor is given as ‘Selection’. The hyperlink is specified as ‘Home’ sheet and range A1.

Step 4: Run the code and sheet sub will be shown the hyperlink as below.

Example #3 – Hyperlink with Multiple Worksheets

If you want to create hyperlink across multiple worksheets it is also possible. In this example, we have multiple sheets within the same workbook. Different type of excel functions exists so from the main worksheet ‘Functions’. Let’s try to create a hyperlink to the different worksheet named with different functions using VBA code:

The multiple worksheets are named as below with different excel function names

Since we want to create a hyperlink to each worksheet it’s difficult to repeat the code. Follow the below steps to create a hyperlink using VBA Code in Excel:

Step 1: Create a variable to deal with worksheet easily.

Code:

Private Sub

hyper2()

Dim

As Worksheet

End Sub

Step 2: Now we want to select the main page which acts as an index page and select the cell range A1.

Code:

Private Sub

hyper2()

Dim

As Worksheet

Worksheets("Functions").Select Range("A1").Select

End Sub

Code:

Private Sub

hyper2()

Dim

As Worksheet

Worksheets("Functions").Select Range("A1").Select

For Each

ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell

End Sub

Step 4: Provide the parameter values to create a hyperlink for each worksheet. Since hyperlink starts from active cell anchor=Active cell, the address is given as ” “.

Code:

Private Sub

hyper2()

Dim

As Worksheet

Worksheets("Functions").Select Range("A1").Select

For Each

ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=""

End Sub

Step 5: The hyperlink is looped through worksheet so we should give subaddress as sheet names. To get the sheet names we can use the variable ws and cell range as A1. The sheet name will have referred with a single quotation. Sheet name and range will be specified and also closed with a single quotation.

Code:

Private Sub

hyper2()

Dim

As Worksheet

Worksheets("Functions").Select Range("A1").Select

For Each

ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & chúng tôi & "!A1" & ""

End Sub

Step 6: To get the hyperlink with sheet name gives TextToDisplay as ws.Name

Code:

Private Sub

hyper2()

Dim

As Worksheet

Worksheets("Functions").Select Range("A1").Select

For Each

ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & chúng tôi & "!A1" & "", TextToDisplay:=ws.Name

End Sub

This code will store hyperlink for each worksheet in the same cell A1.

Step 7: To change this each sheet to different cell down one cell from the active cell.

Code:

Private Sub

hyper2()

Dim

As Worksheet

Worksheets("Functions").Select Range("A1").Select

For Each

ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & chúng tôi & "!A1" & "", TextToDisplay:=ws.Name ActiveCell.Offset(1, 0).Select

End Sub

Things to Remember

Hyperlink property of active cell used to create hyperlinks in VBA.

Hyperlink help to move within the workbook easily.

Recommended Articles

This is a guide to VBA Hyperlinks. Here we learn how to create hyperlinks in Worksheet Using VBA Code to quickly move from one sheet to another sheet along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

Learn Hive Query To Unlock The Power Of Big Data Analytics

Introduction

Given the number of large datasets that data engineers handle on a daily basis, it is no doubt that a dedicated tool is required to process and analyze such data. Some tools like Pig, One of the most widely used tools to solve such a problem is Apache Hive which is built on top of Hadoop.

Apache Hive is a data warehousing built on top of Apache Hadoop. Using Apache Hive, you can query distributed data storage, including the data residing in Hadoop Distributed File System (HDFS), which is the file storage system provided in Apache Hadoop. Hive also supports the ACID properties of relational databases with ORC file format, which is optimized for faster querying. But the real reason behind the prolific use of Hive for working with Big Data is that it is an easy-to-use querying language.

Apache Hive supports the Hive Query Language, or HQL for short. HQL is very similar to SQL, which is the main reason behind its extensive use in the data engineering domain. Not only that, but HQL makes it fairly easy for data engineers to support transactions in Hive. So you can use the familiar insert, update, delete, and merge SQL statements to query table data in Hive. In fact, the simplicity of HQL is one of the reasons why data engineers now use Hive instead of Pig to query Big data.

So, in this article, we will be covering the most commonly used queries which you will find useful when querying data in Hive.

Learning Objectives

Get an overview of Apache Hive.

Get familiar with Hive Query Language.

Implement various functions in Hive, like aggregation functions, date functions, etc.

Table of Contents Hive Refresher

Hive is a data warehouse built on top of Apache Hadoop, which is an open-source distributed framework.

Hive architecture contains Hive Client, Hive Services, and Distributed Storage.

Hive Client various types of connectors like JDBC and ODBC connectors which allows Hive to support various applications in a different programming languages like Java, Python, etc.

Hive Services includes Hive Server, Hive CLI, Hive Driver, and Hive Metastore.

Hive CLI has been replaced by Beeline in HiveServer2.

Hive supports three different types of execution engines – MapReduce, Tez, and Spark.

Hive supports its own command line interface known as Hive CLI, where programmers can directly write the Hive queries.

Hive Metastore maintains the metadata about Hive tables.

Hive metastore can be used with Spark as well for storing the metadata.

Hive supports two types of tables – Managed tables and External tables.

The schema and data for Managed tables are stored in Hive.

In the case of External tables, only the schema is stored by Hive in the Hive metastore.

Hive uses the Hive Query Language (HQL) for querying data.

Using HQL or Hiveql, we can easily implement MapReduce jobs on Hadoop.

Let’s look at some popular Hive queries.

Simple Selects

In Hive, querying data is performed by a SELECT statement. A select statement has 6 key components;

SELECT column names

FROM table-name

GROUP BY column names

WHERE conditions

HAVING conditions

ORDER by column names

In practice, very few queries will have all of these clauses in them, simplifying many queries. On the other hand, conditions in the WHERE clause can be very complex, and if you need to JOIN two or more tables together, then more clauses (JOIN and ON) are needed.

All of the clause names above have been written in uppercase for clarity. HQL is not case-sensitive. Neither do you need to write each clause on a new line, but it is often clearer to do so for all but the simplest of queries.

Over here, we will start with the very simple ones and work our way up to the more complex ones.

Simple Selects ‐ Selecting Columns

Amongst all the hive queries, the simplest query is effectively one which returns the contents of the whole table. Following is the syntax to do that –

SELECT * FROM geog_all;

It is better to practice and generally more efficient to explicitly list the column names that you want to be returned. This is one of the optimization techniques that you can use while querying in Hive.

SELECT anonid, fueltypes, acorn_type FROM geog_all; Simple Selects – Selecting Rows

In addition to limiting the columns returned by a query, you can also limit the rows returned. The simplest case is to say how many rows are wanted using the Limit clause.

SELECT anonid, fueltypes, acorn_type FROM geog_all LIMIT 10;

This is useful if you just want to get a feel for what the data looks like. Usually, you will want to restrict the rows returned based on some criteria. i.e., certain values or ranges within one or more columns.

SELECT anonid, fueltypes, acorn_type FROM geog_all WHERE fueltypes = "ElecOnly";

The Expression in the where clause can be more complex and involve more than one column.

SELECT anonid, fueltypes, acorn_type FROM geog_all SELECT anonid, fueltypes, acorn_type FROM geog_all

Notice that the columns used in the conditions of the WHERE clause don’t have to appear in the Select clause. Other operators can also be used in the where clause. For complex expressions, brackets can be used to enforce precedence.

SELECT anonid, fueltypes, acorn_type, nuts1, ldz FROM geog_all WHERE fueltypes = "ElecOnly" AND acorn_type BETWEEN 42 AND 47 AND (nuts1 NOT IN ("UKM", "UKI") OR ldz = "--"); Creating New Columns

It is possible to create new columns in the output of the query. These columns can be from combinations from the other columns using operators and/or built-in Hive functions.

SELECT anonid, eprofileclass, acorn_type, (eprofileclass * acorn_type) AS multiply, (eprofileclass + acorn_type) AS added FROM edrp_geography_data b;

A full list of the operators and functions available within the Hive can be found in the documentation.

When you create a new column, it is usual to provide an ‘alias’ for the column. This is essentially the name you wish to give to the new column. The alias is given immediately after the expression to which it refers. Optionally you can add the AS keyword for clarity. If you do not provide an alias for your new columns, Hive will generate a name for you.

Although the term alias may seem a bit odd for a new column that has no natural name, alias’ can also be used with any existing column to provide a more meaningful name in the output.

Tables can also be given an alias, this is particularly common in join queries involving multiple tables where there is a need to distinguish between columns with the same name in different tables. In addition to using operators to create new columns, there are also many Hive built‐in functions that can be used.

Hive Functions

You can use various Hive functions for data analysis purposes. Following are the functions to do that.

Simple Functions

Let’s talk about the functions which are popularly used to query columns that contain string data type values.

Concat can be used to add strings together.

SELECT anonid, acorn_category, acorn_group, acorn_type, concat (acorn_category, ",", acorn_group, ",", acorn_type) AS acorn_code FROM geog_all;

substr can be used to extract a part of a string

SELECT anon_id, advancedatetime, FROM elec_c;

Examples of length, instr, and reverse

SELECT anonid, acorn_code, length (acorn_code), instr (acorn_code, ',') AS a_catpos, instr (reverse (acorn_code), "," ) AS reverse_a_typepo

Where needed, functions can be nested within each other, cast and type conversions.

SELECT anonid, substr (acorn_code, 7, 2) AS ac_type_string, cast (substr (acorn_code, 7, 2) AS INT) AS ac_type_int, substr (acorn_code, 7, 2) +1 AS ac_type_not_sure FROM geog_all; Aggregation Functions

Aggregate functions are used to perform some kind of mathematical or statistical calculation across a group of rows. The rows in each group are determined by the different values in a specified column or columns. A list of all of the available functions is available in the apache documentation.

SELECT anon_id, count (eleckwh) AS total_row_count, sum (eleckwh) AS total_period_usage, min (eleckwh) AS min_period_usage, avg (eleckwh) AS avg_period_usage, max (eleckwh) AS max_period_usage FROM elec_c GROUP BY anon_id;

In the above example, five aggregations were performed over the single column anon_id. It is possible to aggregate over multiple columns by specifying them in both the select and the group by clause. The grouping will take place based on the order of the columns listed in the group by clause. What is not allowed is specifying a non‐aggregated column in the select clause that is not mentioned in the group by clause.

Unfortunately, the group by clause will not accept alias’.

But the Order by clause does.

The Distinct keyword provides a set of a unique combination of column values within a table without any kind of aggregation.

SELECT DISTINCT eprofileclass, fueltypes FROM geog_all; Date Functions

Hive provides a variety of date-related functions to allow you to convert strings into timestamps and to additionally extract parts of the Timestamp.

unix_timestamp returns the current date and time – as an integer!

from_unixtime takes an integer and converts it into a recognizable Timestamp string

SELECT unix_timestamp () AS currenttime FROM sample_07 LIMIT 1; SELECT from_unixtime (unix_timestamp ()) AS currenttime FROM sample_07 LIMIT 1;

There are various date part functions that will extract the relevant parts from a Timestamp string.

SELECT anon_id, from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy')) AS proper_date, year (from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy'))) AS full_year, month (from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy'))) AS full_month, day (from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy'))) AS full_day, last_day (from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy'))) AS last_day_of_month, date_add ( (from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy'))),10) AS added_days FROM elec_days_c ORDER BY proper_date; Conclusion

In the article, we covered some basic Hive functions and queries. We saw that running queries on distributed data is not much different from running queries in MySQL. We covered some same basic queries like inserting records, working with simple functions, and working with aggregation functions in Hive.

Key Takeaways

Hive Query Language is the language supported by Hive.

HQL makes it easy for developers to query on Big data.

HQL is similar to SQL, making it easy for developers to learn this language.

I recommend you go through these articles to get acquainted with tools for big data:

Frequently Asked Questions

Q1. What queries are used in Hive?

A. Hive supports the Hive Querying Language(HQL). HQL is very similar to SQL. It supports the usual insert, update, delete, and merge SQL statements to query data in Hive.

Q2. What are the benefits of Hive?

A. Hive is built on top of Apache Hadoop. This makes it an apt tool for analyzing Big data. It also supports various types of connectors, making it easier for developers to query Hive data using different programming languages.

Q3. What is the difference between Hive and MapReduce?

A. Hive is a data warehousing system that provides SQL-like querying language called HiveQL, while MapReduce is a programming model and software framework used for processing large datasets in a distributed computing environment. Hive also provides a schema for data stored in Hadoop Distributed File System (HDFS), making it easier to manage and analyze large datasets.

How To Use The Mysql Replace & Query Examples

Introduction to MySQL REPLACE

The MySQL REPLACE function is one of the string functions used to replace all existences of a substring within the main string to result in a new substring.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The MySQL REPLACE function includes three parameters. The first parameter represents the main string where the replacement will occur. The other two parameters specify the substring to be replaced within the first string and the new substring for replacement.

This task in MySQL makes a case-sensitive function implementation while the string replacement process.

Hence, MySQL allows us to replace an old string with the new one in a column of the database table to perform any handy search and replace the needed one with the existing record value.

Syntax

The MySQL REPLACE function is used with the following syntax:

REPLACE(Original_String, Substring1, Subtring2)

Original_String: This term denotes the main string in which the new one replaces the old string.

Substring1: This is the required occurrence of the substring to be replaced present in the original string.

Substring2: This is the required substring with the help of which a new substring can be replaced from the old one.

How does the REPLACE function work in MySQL?

First, Let us check a simple example to learn the implementation of REPLACEfunction in MySQL. The query is written as follows, where the original string is “VBN” and the substring “B” within “VBN” will be replaced by the new string “X”:

SELECT REPLACE("VBN HJB", "B", "X");

Result:

As per the above query, the valid expression is specified to replace the old string with the new one in the main string. The function executes to replace all the occurrences of the second argument in a specified string using the desired new one.

Thus, Using the REPLACE function in MySQL, we can effectively handle spelling mistakes in columns and perform searches for words that may not be accurate or valid. We can update the records by replacing specific characters with appropriate ones to obtain the desired results.

In this way, for a column value replacement, let us take the statement as follows:

UPDATE Person SET Person_Address = REPLACE(Person_Address,'Jaiput','Jaipur');

This above helps to find all the occurrences of a spelling error in the column of the address of the table person and updates it with the correct one. For the function, the first parameter defines the specified column name without quotes, and the other two denote the substrings, which are responsible for replacing each other to produce a new string.

We should know that if we apply quotes with the field column in the function like ‘Person_Address’, then. As a result, the values of that column will be updated by this ‘Person_Address’. This will cause a sudden data loss of that column in the table.

Note that MySQL REPLACE function will make logical sense only if there exists a Primary Key or a Unique key in the table database so that the function can determine a new row without going through duplicity to make a replacement using indexes of the table; otherwise, it will be corresponding to an INSERT statement.

Also, learn that this function does not upkeep regular expression. If we want to substitute any text string using a specific pattern, we need to use a user-defined MySQL function, i.e., UDF from an external library.

Examples of MySQL REPLACE

We will execute the below queries, which show various ways to use this MySQL REPLACE string function in a database table:

Let us take a basic example of using MySQL REPLACE function:

Code:

Output:

2. Correcting invalid String characters within a word

We are replacing the word ‘Learnint’, correcting a spelling error with the ‘g’ substring, and removing it.

Code:

SELECT REPLACE("Learnint","t", "g");

Output:

3. Replace the substring containing numbers

Let us suppose the below query to replace a specific number string:

Code:

SELECT REPLACE("477", "7", "9");

Output:

4. Replace string using the Column name of a table and SELECT Statement

Considering a sample table named Books, we will use the MySQL REPLACE function in the query to show the string exchange of column values having the substring within it.

Code:

select * from Books;

For example, the query is executed as below to display the result:

Code:

SELECT BookID,BookName,Language, REPLACE(Language, 'English', 'Eng') AS 'Replaced Language' FROM books;

Output:

5. Replace string Example With SELECT & WHERE statement

We are now using the MySQL REPLACE function for a column of the Books table using a SELECT statement and will interchange the book name column string having a substring within the value to a new substring applied to the search. Table Books:

Code:

select * from Books;

The query is below:

Code:

SELECT BOOKID, BOOKNAME, REPLACE(BOOKNAME,'Science','Sci') FROM Books2 WHERE BOOKNAME = 'Nuclear Science';

Output:

6. MySQL REPLACE function with the UPDATE statement

Code:

UPDATE TableName SET ColumnName = REPLACE(ColumnName, Substring1, Substring2) WHERE CondExpr;//condExprs: Conditional expression

For example, taking the sample table Person, we perform the below query:

Code:

UPDATE Books2 SET BOOKNAME = REPLACE(BOOKNAME,'Networking','Computer Network'); select * from Books2;

Output:

Code:

update Books set PRICE = '2000' where BOOKID = 107; select * from Books;

Output:

7. Case Sensitive Error

If we execute the below query in MySQL, then the result will be as follows:

Code:

SELECT REPLACE('APPLE', 'e', 'g');

Output:

In this way, The MySQL REPLACE function supports a case-sensitive search for a string that needs to be replaced, resulting in a new value. So, we must specify valid string expressions on which we want to perform the search and replacement. If not, the result will be the same as the original one.

Conclusion

This article taught us about the MySQL REPLACE function and how to exchange an old string with a new one.

Thus, the MySQL REPLACE function helps to substitute text in a table column for tasks such as interchanging the obsolete link, fixing any spelling error in the database records, etc.

Recommended Articles

We hope that this EDUCBA information on “MySQL REPLACE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

How To Format Date In Javascript (With Examples)

Date formatting is a common operation in JavaScript to make the dates look different depending on the context. Traditionally, you might think you need to use a library such as Date-fns, to format dates.

But for smaller projects, using a library to format dates can be overkill because JavaScript also has some built-in data formatting options.

Today, you are going to learn how to use native JavaScript to format dates.

For example, let’s format the current date in the US to look like this: Saturday, Oct 15, 2023:

const today = new Date().toLocaleDateString('en-us', { weekday:"long", year:"numeric", month:"short", day:"numeric"}) console.log(today)

Output:

Saturday, Oct 15, 2023

This comprehensive guide teaches you how to format dates in different locales.

How to Format Dates in JavaScript?

Obtaining or creating a date object in JavaScript is not a big deal. You can do this with one line of code by using the built-in Date() constructor.

For example:

const today = new Date() console.log(today)

Output:

2023-10-15T19:02:16.589Z

When you run the above code, JavaScript uses your browser’s time zone and shows the complete date information as a string.

Showing the date in the above format isn’t usually something you want to show your end users.

But the trickier part is to format the date properly. This is why developers might end up using a date formatting library in their projects. Even though many use external libraries, you can accomplish practically all date formatting tasks by using native JavaScript code with no hassle.

Format Dates with .toLocaleDateString() Method

In case the default formatting of the date doesn’t meet your (users’) eye, you can use the built-in Date.toLocaleDateString() method to format your dates.

There are three ways you can use this method:

Date.toLocaleDateString(). Without any arguments, this method returns the default language-sensitive format of the date object.

Date.toLocaleDateString(locales). The first optional argument locales specify the localization (or localizations) of the date to display it in the format of other countries. You need to specify the localization as a standard locale code (such as en-US or fi-FI)

Date.toLocaleDateString(locales, options). The second optional parameter, options, lets you customize the output format. For example, you can choose to show only the day, hour, and minute and leave other time components out.

As you can see, these three ways of calling the .toLocaleDateString() method let you fully customize the date to be shown.

The best way to learn how to use this date formatting strategy is by trying it yourself.

Here are some examples of formatting dates to different time zones:

const currentDate = new Date(); const options = { weekday: 'long', year: 'numeric', month: 'short', day: 'numeric' }; console.log(currentDate.toLocaleDateString('fi-FI', options)); console.log(currentDate.toLocaleDateString('ar-EG', options)) console.log(currentDate.toLocaleDateString('en-us', options));

Here are all the parameter names and types available when specifying the options argument in the .toLocaleDateString() method:

{ weekday: 'short', day: 'numeric', year: 'numeric', month: 'long', hour: 'numeric', minute: 'numeric', second: 'numeric', }

With this in mind, let’s see one more example. This time, let’s show the time in Great Britain such that:

The weekday is in a long format.

The year is a 2-digit string.

The month is in a short format.

The day is in numeric format.

The hour and minute are 2-digit strings.

const currentDate = new Date(); const options = { weekday: 'long', year: '2-digit', month: 'short', day: 'numeric', hour: '2-digit', minute: '2-digit' }; console.log(currentDate.toLocaleDateString('en-GB', options));

Output:

Saturday, 15 Oct 22, 19:32 Need More Formatting Options?

Okay, I promised this tutorial is all about native JavaScript.

But I think this tutorial wouldn’t be complete without mentioning an external library you might want to use.

For example, if you want to easily customize the date format to something like yyyy/mm/dd hh:mm:ss, you might want to use the moment library.

When you have installed the moment library, you can format date components easily on the fly.

For example:

moment().format('MMMM Do YYYY, h:mm:ss a');

Notice that moment has a great installation guide and some useful examples on their homepage:

Conclusion

In JavaScript, getting the current date is easy by using the new Date() constructor. The problem with this is the date format. The default date formatting isn’t something you want to show to your end users.

Instead of relying on a third-party library, you can use native JavaScript easily to format dates.

To format dates in JavaScript, use the .toLocaleDateString() method of the Date object. You can pass this method two optional arguments:

locales for specifying the localization(s) in which you want to show the time.

options for formatting the date components more specifically.

In this case, consider installing the free moment library. This popular date library handles common data formatting tasks with care. Using a library overcomes silly mistakes with date formatting scripts found online.

Thanks for reading. Happy coding!

How Apply Works In Kotlin With Examples

Introduction to Kotlin apply

Web development, programming languages, Software testing & others

Syntax

In kotlin language, it has a lot of functions, classes, and other keywords for utilising the kotlin logic in the application. Like that, apply is one of the scope functions that can be used to configure the object once initialized and returns the object itself.

class name{ var vars:datatype; fun demo(){ ---some logic codes depends on the requirement— } } name().apply{ ---some codes--- }.demo()

The above codes are the basic syntax for utilising the apply() method in the kotlin class object initialization and return type.

How does apply work in Kotlin?

The kotlin application has some default functions that can be used to approach the top-level site in the file. It is needed for to create the class in to hold the reference of that class method. Kotlin apply is one of the extension functions that can be used on any of the types it runs on the object reference that can be of receiver format into the expression, and it returns the object reference. The apply function is similar to the run functionality, and it is only in terms of referring to the context of the object using “this” and other keywords like “it” in providing the null safety checks.

Apply keyword used in many cases that can be used with various scenarios, and it should be returned with an instance of Intent and an Alert dialog, etc. When we add the specific attributes in the application to them, it can be improved approach from the code snippet helps avoid variable names. The redundancy it thereby enhancing the code readability and the principle of code cleaning. We can see the other keywords and similar methods like run that accepts the return statement whereas the apply that does not accept a return object.

Examples of Kotlin apply

Given below are the examples of Kotlin apply:

Example #1

Code:

class Firstclass { infix fun demo(s: String) { } fun example() { this demo "Please enter your input types" demo("Sivaraman") } } class Worker { var workerName: String = "" var workerID: Int = 0 fun workerDetails(){ lst.add("Employee Name is: XX, Employee ID is:001") lst.add("Employee Name is: XA, Employee ID is:001n") lst.add("Employee Name is: YB, Employee ID is:002n") lst.add("Employee Name is: YC, Employee ID is:003n") for(x in lst) print("The employee lists are iterated and please find your output results $x ") } } { Worker().apply{ this.workerName = "First Employee is Sivaraman" this.workerID = 52 }.workerDetails() data class First(var inp1 : String, var inp2 : String,var inp3 : String) var first = First("Welcome To My Domain its the first example that related to the kotlin apply() function","Have a nice day users","Please try again") first.apply { chúng tôi = "Welcome To My Domain its the first example that related to the kotlin apply() function" } println(first) with(first) { inp1 = "Please enter your input types" inp2 = "Please enter your input types" } println(first) }

Output:

In the above example, we performed the employee details using the default methods.

Example #2

Code:

interface firstInterface { val x : Int val y : String get() = "Welcome To My Domain its the second example that related to the kotlin also() method" } interface secondInterface { fun Sample1(){ println("Your Sample1 Method") } } class Second : firstInterface, secondInterface { override val x : Int get() = 52 override fun Sample1() { println("Have a Nice Day users please try again") } private val res = "firstInterface Second" fun eg() = "This is the second we discussed regarding kotlin also() method" } class Business { var bName: String = "" var GST: String = "" var Location: String = "" var year: Int = 0 fun enteredNewEmployees(bn: String, gst: String, loc: String, yr: Int) { bName = bn GST = gst Location = loc year = yr println("Please see the below new business name: $bName") println("Please see the Business id: $GST") println("Your Location is: $Location") println("The Cross year of the new business is : $year") } fun NewBusinessName(bn: String) { this.bName = bn } } { data class Car(var carName: String, var CarModel : String) var nw = Car("Benz", "AMGE53") nw.apply { CarModel = "RangeRover Evoque" } println(nw) nw.also { it.CarModel = "Audi A6" } println(nw) val ob = Second() ob.Sample1() var ob1 = Business() var ob2 = Business() ob1.enteredNewEmployees("VSR Garments", "33ACKPV67253278", "TUP", 1994) ob2.NewBusinessName("Raman") println("bName of the new Business: ${ob2.bName}") val s = Second() println(s) println(s.eg()) }

Output:

Example #3

Code:

{ data class Third(var str1: String, var str2 : String) var third = Third(“Siva”, “Raman”) println(“Welcome To My domain its the third example that related to the kotlin apply() method”) third.apply { str2 = “Sivaraman” } println(third) third.also { chúng tôi = “Dell” } println(third) with(third) { str1 = “Sivaraman is the Employee working in XX company” str2 = “He is using the DELL Laptop which provided by his office” } }

Output:

In the final example, we used default methods like also(), apply(), and with() methods for to perform the user operations in the application.

Conclusion

In kotlin language has some default scope functions for performing the operations in the kotlin application. Moreover, it has some default extension functions for doing something with an object, and it returns the value. Also, if we need to perform some other operations on an object and it returns some other object, we can utilise it.

Recommended Articles

This is a guide to Kotlin apply. Here we discuss the introduction, syntax, and working of apply in Kotlin along with different examples for better understanding. You may also have a look at the following articles to learn more –

Update the detailed information about How To Create Table In Hive With Query Examples? on the Katfastfood.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!