Hello Oracle users,
Hope you are doing well.
Although, this is about Microsoft Excel CONCATENATE() function but it helps developers to simplify their code. Using this function we can prepare STRINGS and those STRINGS can be used in SQL codes.
Use of CONCATENATE
CONCATENATE() is used to combine values from multiple cells in one single cell. Also, we can combine multiple CHAR/NUMBER values in one cell. See below.
If we want any additional CHAR values in the output which is not available in the any cell, we must put that CHAR values in between DOUBLE QUOTATION MARKS (“ ”). You can see in the 1st and 2nd row. I have added space between column A and B while combining the values. An SPACE is considered as a CHAR value. In case of any Numbers, we can omit DOUBLE QUOTATION MARKS (“ ”).
There is an another method of concatenation/combining values of multiple cells. We can use ampersand ( & ) between/among values to combine them.
There is same approach of adding any additional CHAR/Number values to the output.
We can use this function effectively while working with so many SQL LIKE expression, a number of values in IN function or when we have to query data from any database based on different keyword/values.
Let’s do an example and see how this Excel function is helpful. There are some keywords/expressions listed below. These expressions are for wrong/bad email addresses. In general, we do not create such email address. So we need to find out those email addresses which contains these keywords/expressions from an Oracle table. We will use Oracle’s Like function to find out those email addresses.
Let’s create a SQL query with above keywords.
Just copy the query from Column B and put after the WHERE clause in the main query. So final query will go like this.
SELECT * FROM table_name WHERE email_address LIKE '%declined%' OR email_address LIKE '%default%' OR email_address LIKE '%donotemail%' OR email_address LIKE '%donotmail%' OR email_address LIKE '%dummies%' OR email_address LIKE '%duplicate%' OR email_address LIKE '%email_none%' OR email_address LIKE '%er.er%' OR email_address LIKE '%grzmail%';
It is just an example. We can use this function in many other ways.
The output of CONCATENATE() is produced in CHAR value. It does not matter what values you use to combine. Though, it does not matter when you want to use output in your SQL query but it matters a lot when you just work in Excel.
If you have any questions please comment below or write me an email.